﻿/*ESRPConnectionString*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
/*Modificari pana la data de 08.08.2010*/
/*
T:ValoriDimensiuni  + CodUnitate
T:Documente + [Dublare_Documente_Furnizor_Numar_Data]
T:RegimFiscal + [CuTva]

SP: +[fares_CautaArticole]
SP: +[fares_GenereazaJurnalCumparari] 
SP: +[fares_GenereazaJurnalVanzari]
SP: +-[GetBalanta]
SP: +-{InitConturiCoresp] - se adauga cont rezumat implicit
SP: +-fares_FiltreazaDocumente
*/


SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON


DECLARE @cmd varchar(8000)
IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetOrderString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
SET @cmd ='CREATE FUNCTION [dbo].[GetOrderString] 
(
	@Cont TipCont	
)
RETURNS TipCont
AS
BEGIN
	DECLARE @Ret TipCont 
	SET @Ret=@Cont 
	IF CHARINDEX(''.'',@cont)<1 
		BEGIN 
		SET @Ret = REPLICATE(''0'', 40-LEN(@Cont)) + @Cont	
		END
	ELSE
		BEGIN
			DECLARE @pointPoz int
			DECLARE @bucCont TipCont
			--numara cate puncte are
			DECLARE @i int,@Cate int
			SET @i=1
			SET @Cate=0
			WHILE @i<=LEN(@Ret)
			BEGIN
				IF SUBSTRING(@Ret,@i,1)=''.''
					SET @Cate=@Cate+1
				SET @i=@i+1
			END
			SET @Cate = 40/(@Cate+1)
			SET @Ret = ''''	
			SET @pointPoz = CHARINDEX(''.'',@Cont)
			WHILE (@pointPoz < LEN(@Cont)) AND (@pointPoz>0)
			BEGIN
				SET @bucCont = REPLICATE(''0'',@Cate-@pointPoz+1) + SUBSTRING(@Cont,1,@pointPoz-1)
				SET @Cont=SUBSTRING(@Cont,@pointPoz+1,LEN(@Cont))
				SET @pointPoz = CHARINDEX(''.'',@Cont)
				SET @Ret = @Ret + @bucCont
			END
			
			SET @Ret = @Ret + REPLICATE(''0'',@Cate-LEN(@Cont)) + @Cont
		END

	

	-- Return the result of the function
	RETURN @Ret

END'
EXEC(@Cmd)
END
GO


IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Banci]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Banci](
		[IdBanca] [smallint] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](100) NOT NULL,
		[BIC] [varchar](4) NOT NULL,
	 CONSTRAINT [PK_Banci] PRIMARY KEY CLUSTERED 
	(
		[IdBanca] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	SET IDENTITY_INSERT [Banci] ON
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(0,'Nedefinit','----')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(1,'ATE BANK ROMANIA S.A.','MIND')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(2,'BRD - Groupe Societe Generale S.A.','BRDE')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(3,'Banca Comerciala Romana S.A.','RNCB')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(4,'RAIFFEISEN BANK SA','RZBR')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(5,'UniCredit Tiriac Bank S.A.','UNCR')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(6,'BANK LEUMI ROMANIA S.A.','DAFB')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(7,'Bancpost S.A.','BPOS')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(8,'Banca Romaneasca S.A.','BRMA')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(9,'Trezorerie','TREZ')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(10,'RBS BANK (ROMANIA) S.A.','ABNA')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(11,'Banca Transilvania S.A.','BTRL')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(12,'CEC Bank','CECE')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(13,'ALPHA BANK ROMANIA','BUCU')
	INSERT INTO [Banci] ([IdBanca],[Denumire],[BIC])VALUES(14,'ATCPMB','ATCP')
	SET IDENTITY_INSERT [Banci] OFF
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Documente' AND COLUMN_NAME = 'ValoareTotalaCuAdaos')
BEGIN
ALTER TABLE dbo.Documente ADD
	ValoareTotalaCuAdaos decimal(25,5) NOT NULL DEFAULT 0
END
GO


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TipDocumentPlata]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[TipDocumentPlata](
		[IDTipPlata] [smallint] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](50) NOT NULL,
	 CONSTRAINT [PK_TipDocumentPlata] PRIMARY KEY CLUSTERED 
	(
		[IDTipPlata] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	SET IDENTITY_INSERT [tipdocumentplata] ON
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(1,'Alte documente de plata')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(2,'Bilet la ordin')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(3,'CARD')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(4,'CEC')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(5,'Chitanta')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(6,'Compensare cu partener')
	INSERT INTO [tipdocumentplata] ([IDTipPlata],[Denumire])VALUES(7,'Ordin de plata')
	SET IDENTITY_INSERT [tipdocumentplata] OFF

END
GO


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DocumentePlata]') AND type in (N'U'))
BEGIN

	CREATE TABLE [dbo].[DocumentePlata](
		[IDDocumentPlata] [int] IDENTITY(1,1) NOT NULL,
		[Numar] [varchar](20) NOT NULL,
		[Data] [datetime] NOT NULL,
		[IDTipPlata] [smallint] NOT NULL,
		[ValoareTotala] [decimal](18, 2) NOT NULL,
		[IDPartener] [bigint] NOT NULL,
		[Reprezentand] [varchar](250) NULL,
		[TipOperatiune] [varchar](10) NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
	 CONSTRAINT [PK_DocumentePlata] PRIMARY KEY CLUSTERED 
	(
		[IDDocumentPlata] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[DocumentePlata]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlata_Parteneri] FOREIGN KEY([IDPartener])
	REFERENCES [dbo].[Parteneri] ([IDPartener])

	ALTER TABLE [dbo].[DocumentePlata] CHECK CONSTRAINT [FK_DocumentePlata_Parteneri]

	ALTER TABLE [dbo].[DocumentePlata]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlata_TipDocumentPlata] FOREIGN KEY([IDTipPlata])
	REFERENCES [dbo].[TipDocumentPlata] ([IDTipPlata])

	ALTER TABLE [dbo].[DocumentePlata] CHECK CONSTRAINT [FK_DocumentePlata_TipDocumentPlata]

	ALTER TABLE [dbo].[DocumentePlata]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlata_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])


	ALTER TABLE [dbo].[DocumentePlata] CHECK CONSTRAINT [FK_DocumentePlata_Unitati]


	ALTER TABLE [dbo].[DocumentePlata] ADD  CONSTRAINT [DF_DocumentePlata_Valoare]  DEFAULT ((0)) FOR [ValoareTotala]


	ALTER TABLE [dbo].[DocumentePlata] ADD  CONSTRAINT [DF_DocumentePlata_TipOperatiune]  DEFAULT ('INC') FOR [TipOperatiune]

	ALTER TABLE [dbo].[DocumentePlata] ADD  CONSTRAINT [DF_DocumentePlata_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DocumentePlataDetaliu]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[DocumentePlataDetaliu](
		[IDDocPlataDetaliu] [int] IDENTITY(1,1) NOT NULL,
		[IDDocument] [bigint] NULL,
		[IDDocumentPlata] [int] NOT NULL,
		[Valoare] [decimal](18, 2) NOT NULL,
	 CONSTRAINT [PK_DocumentePlataDetaliu] PRIMARY KEY CLUSTERED 
	(
		[IDDocPlataDetaliu] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[DocumentePlataDetaliu]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlata] FOREIGN KEY([IDDocumentPlata])
	REFERENCES [dbo].[DocumentePlata] ([IDDocumentPlata])

	ALTER TABLE [dbo].[DocumentePlataDetaliu] CHECK CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlata]

	ALTER TABLE [dbo].[DocumentePlataDetaliu]  WITH CHECK ADD  CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlataDetaliu] FOREIGN KEY([IDDocument])
	REFERENCES [dbo].[Documente] ([IDDocument])

	ALTER TABLE [dbo].[DocumentePlataDetaliu] CHECK CONSTRAINT [FK_DocumentePlataDetaliu_DocumentePlataDetaliu]

	ALTER TABLE [dbo].[DocumentePlataDetaliu] ADD  CONSTRAINT [DF_DocumentePlataDetaliu_Valoare]  DEFAULT ((0)) FOR [Valoare]
END
GO

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TipDimensiune]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[TipDimensiune](
		[IDTipDimensiune] [smallint] IDENTITY(0,1) NOT NULL,
		[Denumire] [varchar](150) NOT NULL,
		[Format] [varchar](10) NOT NULL,
		[Vizibila] [bit] NOT NULL,
	 CONSTRAINT [PK_TipDimensiune] PRIMARY KEY CLUSTERED 
	(
		[IDTipDimensiune] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [IX_TipDimensiune] UNIQUE NONCLUSTERED 
	(
		[Denumire] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[TipDimensiune] ADD  CONSTRAINT [DF_TipDimensiune_Denumire]  DEFAULT ('') FOR [Denumire]

	ALTER TABLE [dbo].[TipDimensiune] ADD  CONSTRAINT [DF_TipDimensiune_Format]  DEFAULT ('') FOR [Format]

	ALTER TABLE [dbo].[TipDimensiune] ADD  CONSTRAINT [DF_TipDimensiune_Vizibila]  DEFAULT ((0)) FOR [Vizibila]

END
GO

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ValoriDimensiuni]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[ValoriDimensiuni](
		[IDValoareDimensiune] [bigint] IDENTITY(1,1) NOT NULL,
		[IDTipDimensiune] [smallint] NOT NULL,
		[Tabela] [varchar](100) NOT NULL,
		[IDCheieTabela] [bigint] NOT NULL,
		[Valoare] [varchar](250) NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
	 CONSTRAINT [PK_ValoriDimensiuni] PRIMARY KEY CLUSTERED 
	(
		[IDValoareDimensiune] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [IX_ValoriDimensiuni] UNIQUE NONCLUSTERED 
	(
		[IDTipDimensiune] ASC,
		[Tabela] ASC,
		[IDCheieTabela] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]



	ALTER TABLE [dbo].[ValoriDimensiuni]  WITH CHECK ADD  CONSTRAINT [FK_ValoriDimensiuni_TipDimensiune] FOREIGN KEY([IDTipDimensiune])
	REFERENCES [dbo].[TipDimensiune] ([IDTipDimensiune])

	ALTER TABLE [dbo].[ValoriDimensiuni] CHECK CONSTRAINT [FK_ValoriDimensiuni_TipDimensiune]

	ALTER TABLE [dbo].[ValoriDimensiuni]  WITH CHECK ADD  CONSTRAINT [FK_ValoriDimensiuni_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])
	ALTER TABLE [dbo].[ValoriDimensiuni] CHECK CONSTRAINT [FK_ValoriDimensiuni_Unitati]

	ALTER TABLE [dbo].[ValoriDimensiuni] ADD  CONSTRAINT [DF_ValoriDimensiuni_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]

END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ValoriDimensiuni' AND COLUMN_NAME = 'CodUnitate')
BEGIN
	ALTER TABLE dbo.ValoriDimensiuni ADD
		CodUnitate smallint NOT NULL DEFAULT 0

	ALTER TABLE dbo.ValoriDimensiuni ADD CONSTRAINT
		FK_ValoriDimensiuni_Unitati FOREIGN KEY
		(
		CodUnitate
		) REFERENCES dbo.Unitati
		(
		CodUnitate
		) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_CautaArticole]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_CautaArticole]
GO

CREATE PROCEDURE [dbo].[fares_CautaArticole]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint,
	@CodArticol varchar(50),
	@Denumire varchar(100),
	@ContArticol TipCont,
	@ContCheltuiala TipCont
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM Articole
	WHERE CodUnitate=@CodUnitate and 
	(@CodArticol='' OR CodArticol LIKE '%' + @CodArticol + '%') AND
	(@Denumire='' OR Denumire LIKE '%' + @Denumire + '%') AND 
	(@ContArticol='' OR Cont = @ContArticol) AND 
	(@ContCheltuiala='' OR ContCheltuiala =@ContCheltuiala)
	
END
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Documente]') AND name = N'Dublare_Documente_Furnizor_Numar_Data')
ALTER TABLE [dbo].[Documente] DROP CONSTRAINT [Dublare_Documente_Furnizor_Numar_Data]
GO

ALTER TABLE [dbo].[Documente] ADD  CONSTRAINT [Dublare_Documente_Furnizor_Numar_Data] UNIQUE NONCLUSTERED 
(
	[IDPartener] ASC,
	[CodUnitate] ASC,
	[NrDoc] ASC,
	[DataDoc] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalCumparari]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalCumparari]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalCumparari] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
docd.*
FROM 
Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
JOIN 

(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19)  THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19) THEN ValoareTVA ELSE 0 END) TVA24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (20) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) ImportTaraScutite,
  SUM(CASE WHEN dd.IDRegimFiscal IN (27) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ObligatBaza,
  SUM(CASE WHEN dd.IDRegimFiscal IN (27) THEN ValoareTVA ELSE 0 END) ObligatTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (29) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCScutit,
  SUM(CASE WHEN dd.IDRegimFiscal IN (22) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRScutit,
  SUM(CASE WHEN dd.IDRegimFiscal IN (24) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFScutit,
  SUM(CASE WHEN dd.IDRegimFiscal IN (30) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCNeimp,
  SUM(CASE WHEN dd.IDRegimFiscal IN (23) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRNeimp,
  SUM(CASE WHEN dd.IDRegimFiscal IN (26) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFNeimp,
  SUM(CASE WHEN dd.IDRegimFiscal IN (28) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCBI,
  SUM(CASE WHEN dd.IDRegimFiscal IN (21) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRBI,
  SUM(CASE WHEN dd.IDRegimFiscal IN (24) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFBI,
  SUM(CASE WHEN dd.IDRegimFiscal IN (28) THEN ValoareTVA ELSE 0 END) BCTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (21) THEN ValoareTVA ELSE 0 END) DRTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (24) THEN ValoareTVA ELSE 0 END) NFTVA

   FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
   GROUP BY dd.IDDocument) docd ON doc.IDDocument = docd.IDDocument
WHERE tipd.IDTipDocument in (0,10) AND doc.CodUnitate=@CodUnitate AND 
doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
   ORDER BY DataDoc
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalVanzari]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalVanzari]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVanzari] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumpTI bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
docd.*
FROM 
Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
JOIN 
(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (10) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) DeducereNoRO,
  SUM(CASE WHEN dd.IDRegimFiscal IN (13) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) FaraDeducereNoRO,
  SUM(CASE WHEN dd.IDRegimFiscal IN (14) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) IntracomScutite1,
  SUM(CASE WHEN dd.IDRegimFiscal IN (15) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) IntracomScutite2,
  SUM(CASE WHEN dd.IDRegimFiscal IN (1) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ScutiteCuDed,
  SUM(CASE WHEN dd.IDRegimFiscal IN (2) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ScutiteFaraDed,
  SUM(CASE WHEN dd.IDRegimFiscal IN (16) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) Neimpozabile,
  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI

   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
   WHERE ((tipd.IDTipDocument in (1,11)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1))
    AND doc.CodUnitate=@CodUnitate 
    AND doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
   ORDER BY DataDoc
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetBalanta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetBalanta]
GO

CREATE PROCEDURE [dbo].[GetBalanta] 
	-- Add the parameters for the stored procedure here
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@pJurnal int,
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@An smallint,
	@Tgc bit
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
DECLARE @CodUnitate smallint
DECLARE @cmd varchar(5000)
DECLARE @strCodUnitate varchar(20)
DECLARE @DtDataStartUI varchar(20)
DECLARE @DtDataEndUI varchar(20)
DECLARE @strCategorie varchar(100)
DECLARE @strAn varchar(10)

SET @CodUnitate=0
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
SET @strCodUnitate = cast(@CodUnitate AS varchar(20))

DECLARE @ConturiTGC AS varchar(2000)
SET @ConturiTGC = ','
SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

DECLARE @ConturiSS AS varchar(2000)
SET @ConturiSS = ','
SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate



SET @DtDataStartUI = cast(YEAR(@pDtDataStartUI) as varchar(4)) + '-' +cast(MONTH(@pDtDataStartUI) as varchar(2)) + '-' + cast(DAY(@pDtDataStartUI) as varchar(2))
SET @DtDataEndUI = cast(YEAR(@pDtDataEndUI) as varchar(4)) + '-' +cast(MONTH(@pDtDataEndUI) as varchar(2)) + '-' + cast(DAY(@pDtDataEndUI) as varchar(2))

SET @strCategorie = LTRIM(RTRIM(cast(@pCategorie as varchar(10))))
SET @strAn = cast(@An as varchar(10))

IF @Tgc=1
	SET @strCategorie = @strCategorie + ',3'

SET @cmd = 'SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
    DebPrecCalc [RulPrecDeb], CrePrecCalc [RulPrecCre], 
    DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
	DebInit + DebPrecCalc+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc + COALESCE(CreCurent, 0) RulTotalCre, 
	dbo.SoldDebit(Tip, DebInit + DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(Tip, DebInit + DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalCre
	FROM
	(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit], 
	sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc,  
	sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
	FROM '
IF @Tgc=1
BEGIN
	DECLARE @cmdCont varchar(2000)
	SET @cmdCont =  
	'(SELECT a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],a.[DebitPrecedent],a.[CreditPrecedent],
	sum(b.DebitInitial) [DebitInitial] ,sum(b.creditInitial) [CreditInitial]
	FROM [Conturi] a JOIN [Conturi] b
	ON dbo.IsContInclus(a.cont,b.cont,a.Terminal)=1 and a.codunitate=b.codunitate
	where a.[CodUnitate]=' + @strCodUnitate +' and a.categorie=3 and b.ContParinte=''N/A''
	group by a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],a.[DebitPrecedent],a.[CreditPrecedent]
	UNION
	SELECT  a2.[Cont],a2.[CodUnitate],a2.[Denumire],a2.[Tip],a2.[Terminal],a2.[ContParinte],a2.[EsteAnalitic],a2.[Categorie],a2.[DebitPrecedent],a2.[CreditPrecedent],
	a2.[DebitInitial] ,a2.[CreditInitial] FROM [Conturi] a2
	WHERE a2.codunitate='+@strCodUnitate +' and a2.categorie=0 )'
	IF @An <> 0 
		SET @cmdCont = REPLACE(@cmdCont,'[Conturi]', '[Conturi' + @strAn + ']')
	
	SET @cmd = @cmd  + @cmdCont
END
ELSE
BEGIN
	IF @An <> 0 
		SET @cmd = @cmd  + '[Conturi' + @strAn + ']'
	ELSE
		SET @cmd = @cmd  + '[Conturi]'
END

SET @cmd = @cmd  + ' c
    LEFT OUTER JOIN 
	(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
	   sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
			(SELECT 			    
				coalesce(SUM(CASE WHEN D.DataDoc<''' + @DtDataStartUI + ''' THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
				coalesce(SUM(CASE WHEN D.DataDoc>=''' + @DtDataStartUI +''' AND D.DataDoc<=''' + @DtDataEndUI +''' THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
				0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
				F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
				FROM '
--[RulajeDoc]
IF @An <> 0 
	SET @cmd = @cmd  + '[RulajeDoc' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[RulajeDoc]'

SET @cmd = @cmd  + ' D INNER JOIN '
--[Rulaje]
IF @An <> 0 
	SET @cmd = @cmd  + '[Rulaje' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[Rulaje]'


SET @cmd = @cmd  + ' F ON D.[IDDocument] = F.[IDDocument] 
				WHERE  d.[CodUnitate] =' +  @strCodUnitate
			
IF 	@pJurnal<>-1	
	SET @cmd = @cmd + 	' AND D.IDJurnal= ' + cast(@pJurnal as varchar(10)) 
					
SET @cmd = @cmd  + ' GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
			UNION
			SELECT 
				0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
				coalesce(SUM(CASE WHEN D.DataDoc < ''' + @DtDataStartUI+''' THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
				coalesce(SUM(CASE WHEN D.DataDoc >=''' + @DtDataStartUI+''' AND D.DataDoc<=''' + @DtDataEndUI +''' THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
                F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
				FROM '
--[RulajeDoc]
IF @An <> 0 
	SET @cmd = @cmd  + '[RulajeDoc' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[RulajeDoc]'

SET @cmd = @cmd  + ' D INNER JOIN '
--[Rulaje]
IF @An <> 0 
	SET @cmd = @cmd  + '[Rulaje' + @strAn + ']'
ELSE
	SET @cmd = @cmd  + '[Rulaje]'

SET @cmd = @cmd  + ' F ON D.[IDDocument] = F.[IDDocument] 
				WHERE d.[CodUnitate] = '+@strCodUnitate 
IF 	@pJurnal<>-1	
	SET @cmd = @cmd + 	' AND D.IDJurnal= ' + cast(@pJurnal as varchar(10)) 
			

SET @cmd = @cmd +  ' GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal) t1
	GROUP BY Cont,CodUnitate,IDJurnal
	) RUL 
	ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
	WHERE  c.[Cont]<>''N/A'' AND Categorie in ('+ @strCategorie + ') AND c.[CodUnitate] ='+ @strCodUnitate + '
	AND c.Cont >= '''+@pStrContStart + ''''
IF @pStrContEnd	<> ''
	SET @cmd = @cmd + ' AND c.Cont <='''+ @pStrContEnd +''''
	
SET @cmd = @cmd +  'GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
	) [balanta] '

DECLARE @cmdFilt varchar(8000)
SET @cmdfilt = ' WHERE ' 
IF @pCfm <> 1
BEGIN
	IF @An <> 0 
		SET @cmdfilt  = @cmdfilt +  ' (EXISTS (SELECT ContDebit, ContCredit FROM Rulaje'+@strAn+' WHERE ((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) AND [CodUnitate]='+@strCodUnitate+') OR ((DebInit<>0) OR (CreInit<>0)))  '
	ELSE
		SET @cmdfilt  = @cmdfilt +  ' (EXISTS (SELECT ContDebit, ContCredit FROM Rulaje WHERE ((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) AND [CodUnitate]='+@strCodUnitate+') OR ((DebInit<>0) OR (CreInit<>0)))  '
END

IF @pAnalitica <> 1
BEGIN
	IF @pCfm <> 1
		SET @cmdfilt  = @cmdfilt +  ' AND '
	IF @Tgc=0
		SET @cmdfilt  = @cmdfilt +  ' (([EsteAnalitic]=0)  OR (dbo.IsContIn('',''+ Cont+'','','''+@ConturiSS+''')<>0)) '
	ELSE
		SET @cmdfilt  = @cmdfilt +  ' (([EsteAnalitic]=0) OR (dbo.IsContIn('',''+ Cont+'','','''+@ConturiTGC+''')<>0)) '

END
IF @pCfmRulaje <> 1
BEGIN
	IF (@pCfm <> 1) OR (@pAnalitica <> 1)
		SET @cmdfilt  = @cmdfilt +  ' AND '
	SET @cmdfilt  = @cmdfilt +  ' (DebCurent<>0 OR CreCurent<>0) '
END

IF @pTerminal <> 0
BEGIN
	IF (@pCfm <> 1) OR (@pAnalitica <> 1) OR (@pCfmRulaje <> 1)
		SET @cmdfilt  = @cmdfilt +  ' AND '
	SET @cmdfilt  = @cmdfilt +  '([Terminal]=1)'
END
IF @cmdfilt <> ' WHERE '
	SET @cmd  = @cmd +  @cmdFilt
SET @cmd  = @cmd +  ' ORDER BY Cont '
PRINT(@cmdFilt)
PRINT(@cmd)
EXEC(@cmd) 
IF @@ERROR != 0 GOTO EROARE  GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare balanta!',16,-1)  GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	GOTO Gata
Gata:

END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RegimFiscal' AND COLUMN_NAME = 'CuTVA')
BEGIN
ALTER TABLE dbo.RegimFiscal ADD
	CuTVA bit NOT NULL CONSTRAINT DF_RegimFiscal_CuTVA DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RegimFiscal' AND COLUMN_NAME = 'TipOperatie')
BEGIN
ALTER TABLE dbo.RegimFiscal ADD
	TipOperatie varchar(10) NOT NULL DEFAULT ''
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RegimFiscal' AND COLUMN_NAME = 'Vizibil')
BEGIN
ALTER TABLE dbo.RegimFiscal ADD
	Vizibil bit NOT NULL DEFAULT 1
END
GO


UPDATE RegimFiscal SET CuTVA=0 WHERE IDRegimFiscal IN (1,2,7,10,13,14,15,16,22,23,25,26,29,30)
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'SalveazaCuUrmatorulNumar')
BEGIN
ALTER TABLE dbo.FaresSetup ADD
	SalveazaCuUrmatorulNumar bit NOT NULL CONSTRAINT DF_FaresSetup_SalveazaCuUrmatorulNumar DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'IDSeriePlati')
BEGIN
ALTER TABLE dbo.FaresSetup ADD
	IDSeriePlati varchar(20) NOT NULL DEFAULT ''
END
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[audit_Document]'))
DROP TRIGGER [dbo].[audit_Document]
GO

CREATE TRIGGER [dbo].[audit_Document] 
   ON  [dbo].[Documente]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @modifDoc bit
	SELECT @modifDoc = SalveazaCuUrmatorulNumar FROM FaresSetup a JOIN inserted i ON
		a.[CodUnitate]=i.CodUnitate
	IF @modifDoc=1
		UPDATE [Documente] SET NrDoc = (SELECT MAX(CAST([NrDoc] AS decimal(18,0)))+1 FROM [Documente] a WHERE 
			a.CodUnitate=[Documente].[CodUnitate]) FROM inserted i
		WHERE i.IDDocument=[Documente].[IDDocument]  AND NOT EXISTS(SELECT * FROM deleted d WHERE d.IDDocument=i.IDDocument)

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT [UserID],'Documente', 'A', IDDocument, getdate() FROM inserted i
		WHERE i.IDDocument not in (SELECT IDDocument from deleted)	 

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT i.[UserID],'Documente', 'M', i.IDDocument, getdate() FROM inserted i
		JOIN deleted d on i.IDDocument=d.IDDocument

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT [UserID],'Documente', 'D', IDDocument, getdate() FROM deleted d
		WHERE d.IDDocument not in (SELECT IDDocument from inserted)	 

	

END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Parteneri_Conturi]') AND parent_object_id = OBJECT_ID(N'[dbo].[Parteneri]'))
ALTER TABLE dbo.Parteneri DROP CONSTRAINT FK_Parteneri_Conturi
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TipDocument' AND COLUMN_NAME = 'SePlateste')
BEGIN
	ALTER TABLE TipDocument ADD SePlateste bit NOT NULL DEFAULT 1
END
GO

DECLARE @cate int

SELECT @Cate = COUNT(*) FROM [TipDocument] WHERE [IDTipDocument] IN (10)
IF @Cate<>1
BEGIN
	SET IDENTITY_INSERT [TipDocument] ON
	INSERT INTO [TipDocument]([IDTipDocument],[Denumire],[TipOperatie],[SePlateste]) VALUES (10,'Bon Fiscal','I',1)
	INSERT INTO [TipDocument]([IDTipDocument],[Denumire],[TipOperatie],[SePlateste]) VALUES (11,'Bon Fiscal','O',1)
	SET IDENTITY_INSERT [TipDocument] OFF

	INSERT INTO [admin_ApplicationDocuments]([IDTipDocument],[ApplicationID]) VALUES (10,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9')
	INSERT INTO [admin_ApplicationDocuments]([IDTipDocument],[ApplicationID]) VALUES (11,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9')


END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[admin_RoleInMenu]') AND type in (N'U'))
	DELETE FROM [admin_RoleInMenu]
GO

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = '892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9'

INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3100,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Documente intrate(cumparare)',NULL,100,'Documente_intrare',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3200,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Documente iesite(vanzare)',NULL,110,'Documente_iesire',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3500,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Incasari, plati',NULL,115,'Incasari_Plati',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3300,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Parteneri',NULL,120,'Parteneri',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4200,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Articole',NULL,130,'Articole',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3400,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Setari',NULL,140,NULL,NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4100,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Import date',NULL,150,'Import',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4800,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Liste',NULL,160,NULL,NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4000,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Unitati functionale',NULL,170,'Unitati_functionale',NULL,NULL)

INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3401,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Parametrizare Modul',3400,20,'ParametrizareModul',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3402,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Banci',3400,21,'Banci',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3403,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Trasee',3400,22,'Trasee',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3404,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Locatii vizitate',3400,23,'Locatii',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3405,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Regim fiscal',3400,24,'Regimuri_Fiscale',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3406,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Coduri CPV',3400,25,'CoduriCPV',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3407,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Categorii Articole',3400,26,'CategoriiArticole',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3408,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Tipuri Dimensiuni',3400,27,'TipuriDimensiuni',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(3409,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Note Contabile',3400,28,'NoteContabile',NULL,NULL)

INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4801,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Categorii Articole',4800,40,'RaportCategoriiArticole',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4802,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Jurnale',4800,41,'RaportJurnale',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4803,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Liste Documente',4800,42,'RaportDocumente',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4804,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Operatiuni Contabile',4800,43,'RaportContabil',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4805,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Facturi',4800,44,'RaportFacturi',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4806,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Operatiuni incasare/plata',4800,45,'RaportOperatiuniIncasarePlata',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4807,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Fisa Partenerului',4800,46,'RaportFisaPartenerului',NULL,NULL)
INSERT INTO [admin_applicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(4808,'892862E5-637B-4BBC-AE2B-2DAC0FFD3DF9','Export Declaratii',4800,47,'ExportDeclaratii',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
GO

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = 'F9B96690-0736-4F91-BC2C-F434651A1AFF'
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2001,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Fisa Mijlocului Fix',NULL,1,'FisaMFix',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2002,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Obiecte de Inventar',NULL,2,'ObiecteInventar',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2003,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Documente',NULL,3,'Documente',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2004,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Inventar - Stoc Faptic',NULL,4,'StocFaptic',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2005,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Rapoarte',NULL,5,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2006,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Setari',NULL,6,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2007,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Unitati functionale',NULL,7,'Unitati_functionale',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2008,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Despre modul',NULL,8,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2109,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Calculul amortizarii',2005,9,'Calculul_Amortizarii',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2100,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Registrul numerelor de inventar',2005,10,'Repartizare_Amortizare',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2101,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Lista de inventar',2005,11,'Lista_Inventar',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2102,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Fisa mijlocului fix',2005,12,'Fisa_Mijlocului_Fix',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2103,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Lista documentelor',2005,13,'Lista_Documentelor',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2104,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Obiecte inventar',2005,14,'Obiecte_Inventar',NULL,NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2204,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Parametrizare modul',2006,14,'Parametrizare',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2205,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Departamente',2006,15,'Departamente',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2206,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Catalog mijloace fixe',2006,16,'Catalog',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2207,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Gestionari',2006,17,'Gestionari',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2208,'F9B96690-0736-4F91-BC2C-F434651A1AFF','Import',2006,18,'Import',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Documente' AND COLUMN_NAME = 'NrDocReceptie')
BEGIN
	ALTER TABLE dbo.Documente ADD NrDocReceptie varchar(20) NULL
	ALTER TABLE dbo.Documente ADD DataDocReceptie datetime NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TipDimensiune' AND COLUMN_NAME = 'Vizibila')
BEGIN
ALTER TABLE dbo.TipDimensiune ADD
	Vizibila bit NOT NULL CONSTRAINT DF_TipDimensiune_Vizibila DEFAULT 1
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'DocumenteDetaliu' AND COLUMN_NAME = 'ValoareTotalaCuAdaos')
BEGIN
ALTER TABLE dbo.DocumenteDetaliu ADD
	ValoareTotalaCuAdaos decimal(25,5) NOT NULL DEFAULT 0
END
GO


DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [TipDimensiune]
IF @Cate=1
BEGIN
	SET IDENTITY_INSERT [TipDimensiune] ON
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(1,'Dimensiune1','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(2,'Dimensiune2','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(3,'Dimensiune3','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(4,'Dimensiune4','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(5,'Dimensiune5','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(6,'Dimensiune6','SL',0)
	INSERT INTO [TipDimensiune] ([IDTipDimensiune],[Denumire],[Format],[Vizibila])VALUES(7,'Dimensiune7','SL',0)
	SET IDENTITY_INSERT [TipDimensiune] OFF
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumenteDimensiuniLinie]'))
DROP VIEW [dbo].[fares_DocumenteDimensiuniLinie]
GO

CREATE VIEW [dbo].[fares_DocumenteDimensiuniLinie]
AS
SELECT     a.Tabela, a.IDCheieTabela, a.CodUnitate, v1.Valoare AS valoare1, v2.Valoare AS valoare2, v3.Valoare AS valoare3, v4.Valoare AS valoare4
FROM         (SELECT DISTINCT Tabela, IDCheieTabela, CodUnitate
                       FROM          dbo.ValoriDimensiuni) AS a LEFT OUTER JOIN
                      dbo.ValoriDimensiuni AS v1 ON v1.IDTipDimensiune = 0 AND v1.CodUnitate = a.CodUnitate AND a.Tabela = v1.Tabela AND 
                      a.IDCheieTabela = v1.IDCheieTabela LEFT OUTER JOIN
                      dbo.ValoriDimensiuni AS v2 ON v2.IDTipDimensiune = 1 AND v2.CodUnitate = a.CodUnitate AND a.Tabela = v2.Tabela AND 
                      a.IDCheieTabela = v2.IDCheieTabela LEFT OUTER JOIN
                      dbo.ValoriDimensiuni AS v3 ON v3.IDTipDimensiune = 2 AND v3.CodUnitate = a.CodUnitate AND a.Tabela = v3.Tabela AND 
                      a.IDCheieTabela = v3.IDCheieTabela LEFT OUTER JOIN
                      dbo.ValoriDimensiuni AS v4 ON v4.IDTipDimensiune = 3 AND v4.CodUnitate = a.CodUnitate AND a.Tabela = v4.Tabela AND 
                      a.IDCheieTabela = v4.IDCheieTabela
WHERE     (a.Tabela = 'Documente')
GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumenteDimensiuni]'))
DROP VIEW [dbo].[fares_DocumenteDimensiuni]
GO


CREATE VIEW [dbo].[fares_DocumenteDimensiuni]
AS
SELECT     a.IDDocument, a.NrDoc, a.DataDoc, a.CodUnitate, a.IDTipDocument, a.NrContract, a.DataContract, a.Observatii, a.DataScadenta, a.NrDocReceptie, a.DataDocReceptie, 
                      dbo.TipDocument.TipOperatie, dbo.TipDocument.Denumire, c.valoare1, c.valoare2, c.valoare3, c.valoare4, p.Denumire AS DenPartener, dd.ValoareTVA, 
                      CASE WHEN a.IDTipDocument IN (10, 11) THEN dd.ValoareTotala - dd.ValoareTVA ELSE dd.ValoareTotala END AS ValoareTotala, dd.CotaTVA, 
                      dd.ValoareTotalaCuAdaos, CASE WHEN a.IDTipDocument IN (10, 11) THEN (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) - (dd.ValoareTotala * 100) 
                      / (dd.CotaTVA + 100) ELSE (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) - dd.ValoareTotala END AS Adaos, (dd.ValoareTotalaCuAdaos * dd.CotaTVA) 
                      / (dd.CotaTVA + 100) AS TVANeexigibil, ar.Denumire AS DenArticol, p.IDPartener, ar.IDArticol
FROM         dbo.Documente AS a INNER JOIN
                      dbo.Parteneri AS p ON a.IDPartener = p.IDPartener INNER JOIN
                      dbo.TipDocument ON a.IDTipDocument = dbo.TipDocument.IDTipDocument LEFT OUTER JOIN
                      dbo.fares_DocumenteDimensiuniLinie AS c ON a.IDDocument = c.IDCheieTabela AND a.CodUnitate = c.CodUnitate INNER JOIN
                      dbo.DocumenteDetaliu AS dd ON a.IDDocument = dd.IDDocument INNER JOIN
                      dbo.Articole AS ar ON dd.IDArticol = ar.IDArticol

GO


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenNoteContabile]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[GenNoteContabile](
		[IDNotaContabila] [int] IDENTITY(1,1) NOT NULL,
		[IDTipDocument] [smallint] NOT NULL,
		[ContDebit] [dbo].[TipCont] NOT NULL,
		[ContCredit] [dbo].[TipCont] NOT NULL,
		[Valoare] [varchar](50) NOT NULL,
	 CONSTRAINT [PK_GenNoteContabile] PRIMARY KEY CLUSTERED 
	(
		[IDNotaContabila] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[GenNoteContabile]  WITH CHECK ADD  CONSTRAINT [FK_GenNoteContabile_TipDocument] FOREIGN KEY([IDTipDocument])
	REFERENCES [dbo].[TipDocument] ([IDTipDocument])

	ALTER TABLE [dbo].[GenNoteContabile] CHECK CONSTRAINT [FK_GenNoteContabile_TipDocument]

END
GO

DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [TipDocument] WHERE [IDTipDocument]<10
IF @Cate= 0 
BEGIN
	SET IDENTITY_INSERT [TipDocument] ON
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(0,'Factura','I',1)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(1,'Factura','O',1)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(2,'Intrare','I',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(3,'Iesire','O',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(4,'Transfer Intern','IO',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(5,'Conservare','IO',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(6,'Reactivare','I',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(7,'Modificare Mijloc Fix','I',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(8,'Modernizare','I',0)
	INSERT INTO [tipDocument] ([IDTipDocument],[Denumire],[TipOperatie],[SePlateste])VALUES(9,'Comanda','O',0)
	SET IDENTITY_INSERT [TipDocument] OFF
END
GO

DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [GenNoteContabile]
IF @Cate= 0 
BEGIN
	SET IDENTITY_INSERT [GenNoteContabile] ON
	INSERT INTO [GenNoteContabile] ([IDNotaContabila],[IDTipDocument],[ContDebit],[ContCredit],[Valoare])VALUES(1,0,'{ART.CNT}','{PRT.CNT}','{DOC.VAL}')
	INSERT INTO [GenNoteContabile] ([IDNotaContabila],[IDTipDocument],[ContDebit],[ContCredit],[Valoare])VALUES(2,0,'{TVA.DED}','{PRT.CNT}','{DOC.TVA}')
	INSERT INTO [GenNoteContabile] ([IDNotaContabila],[IDTipDocument],[ContDebit],[ContCredit],[Valoare])VALUES(3,1,'{PRT.CNT}','{ART.CNT}','{DOC.VAL}')
	INSERT INTO [GenNoteContabile] ([IDNotaContabila],[IDTipDocument],[ContDebit],[ContCredit],[Valoare])VALUES(4,1,'{PRT.CNT}','{TVA.COL}','{DOC.TVA}')
	SET IDENTITY_INSERT [GenNoteContabile] OFF
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportOpConturi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportOpConturi]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportOpConturi] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_DocumenteDimConturi] WHERE ' + @filtru +
	 ' AND [DataDoc]>=''' + @data1 + ''' AND [DataDoc]<=''' + @data2 +''' '
	EXEC(@sel)
END
GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportDocumente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportDocumente]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportDocumente] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_DocumenteDimensiuni] WHERE ' + @filtru +
	 ' AND [DataDoc]>=''' + @data1 + ''' AND [DataDoc]<=''' + @data2 +''' ORDER BY [DataDoc]'
	EXEC(@sel)
END
GO

DROP INDEX IX_Parteneri ON dbo.Parteneri
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_Parteneri ON dbo.Parteneri
	(
	CUI,
	CodUnitate
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_ParteneriProprii]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_ParteneriProprii]
GO

CREATE PROCEDURE [dbo].[fares_ParteneriProprii]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Se insereaza toti partenerii din alte unitati in unitatea proprie
    INSERT INTO [Parteneri]
           ([CUI],[Denumire],[IDBanca],[ContBancar],[Cont],[CodUnitate],[TipPartener],[IDJudet],[IDLocalitate]
           ,[Strada],[Nr],[AtributFiscal],[JudetRegComert],[NrRegComert],[AnRegComert],[NumeDelegat],[SerieAI],[NrAI],[MijlocTransport]
           ,[NrMijlocTransport],[CodPostal],[Telefon],[Fax])
    SELECT [CUI],[Denumire],[IDBanca],[ContBancar],[Cont],@CodUnitate,[TipPartener],[IDJudet],[IDLocalitate]
           ,[Strada],[Nr],[AtributFiscal],[JudetRegComert],[NrRegComert],[AnRegComert],[NumeDelegat],[SerieAI],[NrAI],[MijlocTransport]
           ,[NrMijlocTransport],[CodPostal],[Telefon],[Fax] FROM [Parteneri] WHERE CodUnitate<>@CodUnitate
           AND (IDPartener IN (SELECT IDPartener FROM Documente WHERE CodUnitate=@CodUnitate) OR 
				IDPartener IN (SELECT IDPartener FROM DocumentePlata WHERE CodUnitate=@CodUnitate) OR
				IDPartener IN (SELECT IDFurnizor FROM Ordonantari WHERE CodUnitate=@CodUnitate))

	--modifica partenerii in noii parteneri
	UPDATE [Documente]
		SET IDPartener = b.IDPartener  FROM Parteneri a, Parteneri b
	WHERE  a.IDPartener = [Documente].IDPartener AND a.CUI = b.CUI AND b.CodUnitate = @CodUnitate AND
	[Documente].[CodUnitate]=@CodUnitate AND [Documente].IDPartener NOT IN (SELECT IDPartener FROM Parteneri WHERE @CodUnitate = [CodUnitate])
	
	-- documente plata
	UPDATE [DocumentePlata]
		SET IDPartener = b.IDPartener  FROM Parteneri a, Parteneri b
	WHERE  a.IDPartener = [DocumentePlata].IDPartener AND a.CUI = b.CUI AND b.CodUnitate = @CodUnitate AND
	[DocumentePlata].[CodUnitate]=@CodUnitate AND 
	[DocumentePlata].IDPartener NOT IN (SELECT IDPartener FROM Parteneri WHERE @CodUnitate = [CodUnitate])

	--modifica partenerii in noii parteneri la ordonantari
	UPDATE [Ordonantari]
		SET IDFurnizor = b.IDPartener  FROM Parteneri a, Parteneri b
	WHERE  a.IDPartener = [Ordonantari].IDFurnizor and a.CUI = b.CUI AND b.CodUnitate = @CodUnitate AND
	[Ordonantari].[CodUnitate]=@CodUnitate AND [Ordonantari].IDFurnizor NOT IN (SELECT IDPartener FROM Parteneri WHERE @CodUnitate = [CodUnitate])

	
	---sterge partenerii vechi ca sa nu se dubleze
	DELETE Parteneri FROM Parteneri a WHERE 
	CUI=a.CUI AND CodUnitate<>@CodUnitate AND a.CodUnitate = @CodUnitate AND
	(IDPartener NOT IN (SELECT IDPartener FROM Documente WHERE CodUnitate<>@CodUnitate) AND 
     IDPartener NOT IN (SELECT IDPartener FROM DocumentePlata WHERE CodUnitate<>@CodUnitate) AND
     IDPartener NOT IN (SELECT IDFurnizor FROM Ordonantari WHERE CodUnitate<>@CodUnitate))
 

END
GO




IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumentePlatite]'))
DROP VIEW [dbo].[fares_DocumentePlatite]
GO

CREATE VIEW [dbo].[fares_DocumentePlatite]
AS
SELECT     dbo.DocumentePlataDetaliu.IDDocPlataDetaliu, dbo.DocumentePlataDetaliu.IDDocumentPlata, dbo.DocumentePlataDetaliu.Valoare, 
                      dbo.Documente.NrDoc + '/' + CONVERT(varchar(20), dbo.Documente.DataDoc, 101) AS NrDataDoc, dbo.DocumentePlataDetaliu.IDDocument
FROM         dbo.DocumentePlataDetaliu INNER JOIN
                      dbo.Documente ON dbo.DocumentePlataDetaliu.IDDocument = dbo.Documente.IDDocument

GO


SET IDENTITY_INSERT [admin_ApplicationMenu] ON
DELETE FROM [admin_ApplicationMenu] WHERE [ApplicationId] = '98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959'
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9002,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Conturi',NULL,1,'Evidenta_Conturi',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9003,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Note Contabile',NULL,2,'Evidenta_NotaContabila',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9004,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Rapoarte',NULL,3,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9005,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Situatii Financiare',NULL,4,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9006,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Setari',NULL,5,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9007,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Consultare Arhiva',NULL,6,'Consultare_Arhiva',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9008,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Unitati Functionale',NULL,7,'Unitati_functionale',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9009,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Generare Note Contabile',NULL,8,'GenerareNoteContabile',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9010,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Despre Modul',NULL,9,'Despre_Modul',NULL,NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9500,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Balanta',9004,89,'Rapoarte','1',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9511,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Fisa Contului',9004,90,'Rapoarte','2',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9512,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Cartea Mare',9004,91,'Rapoarte','3',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9513,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Registrul Jurnal',9004,92,'Rapoarte','4',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9514,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Registrul de Casa',9004,93,'Rapoarte','5',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9515,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Extras de Cont',9004,94,'Rapoarte','6',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9516,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Fise Generale',9004,95,'Rapoarte','7',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9517,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Bilant',9004,96,'Rapoarte','8',NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9518,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Operatiuni Diverse',9004,97,'Rapoarte','10',NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9619,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Contul de executie (institutie)',9005,98,'ContExecutie',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9620,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Executie Cheltuieli - simplificata',9005,99,'ExecutieCheltuieli',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9621,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Lista Operatiuni Bugetare',9005,120,'OperatiuniBugetare',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9622,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Cont de Rezultat Patrimonial',9005,121,'ContRezultat',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9623,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Cont de Profit si Pierdere',9005,122,'ContProfit',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9624,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Situatia Veniturilor si cheltuielilor',9005,123,'VenituriSiCheltuieli',NULL,NULL)

INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9725,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Parametrizare Modul',9006,124,'Parametrizare',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9726,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Serii Numerice',9006,125,'SeriiNumerice',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9727,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Conturi Corespondente (Inchidere)',9006,126,'ConturiCorespondente',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9728,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Capitole/Jurnale',9006,127,'Jurnale',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9729,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Parametrizare Liste',9006,128,'Parametrizare_Liste',NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(9730,'98A8F7C6-2EFC-402A-9BA3-7A62CD2D0959','Import',9006,129,'Parametrizare_Import',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InitConturiCoresp]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InitConturiCoresp]
GO

CREATE PROCEDURE [dbo].[InitConturiCoresp]
AS
	SET NOCOUNT ON 
	DECLARE @CodUnitate smallint
	SET @CodUnitate=0
	SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
	
	--PRINT(@CodUnitate) 
	DECLARE @ContEc TipCont
	SELECT @ContEc = Cont FROM ConturiSpeciale WHERE (Tipuri = '{REX}') AND [CodUnitate]=@CodUnitate
	--PRINT(@ContEc)
	--daca nu este definit contul rezultat se adauga unul implicit
	IF @ContEc is null
	BEGIN
		--GOTO ErrorContCoresp
		INSERT INTO [ConturiSpeciale]([Cont],[CodUnitate],[Tipuri],[Descriere])
		SELECT MIN([Cont]),MIN([CodUnitate]),'{REX}','Rezultat patrimonial' FROM Conturi WHERE Denumire like '%rezult%' AND CodUnitate = @CodUnitate

	END	
	DECLARE @ContRez TipCont 

	SELECT TOP 1 @ContRez = Cont FROM [Conturi] WHERE Cont LIKE @ContEc + '%' AND Terminal=1 AND [CodUnitate] = @CodUnitate

	INSERT INTO [ConturiCoresp] ([ContCredit],[ContDebit],[IdJurnal],[Tip],[CodUnitate])
	SELECT Cont AS [ContCredit], 
	(@ContRez) AS [Contdebit],
	[IDJurnal],
	'IC' AS [Tip],
	@CodUnitate
	FROM  [Conturi] a JOIN  (SELECT DISTINCT [IDJurnal] FROM [RulajeDoc] WHERE [CodUnitate]=@CodUnitate) b ON dbo.GetContClass(a.Cont) = '6' AND a.Terminal = 1   AND
	NOT EXISTS( SELECT [IDjurnal],[ContCredit] FROM [ConturiCoresp] x where  [ContCredit]=a.cont 
	and [IDJurnal] = b.[IdJurnal] AND x.[CodUnitate]=@CodUnitate) AND [CodUnitate]=@CodUnitate    
	
	INSERT INTO [ConturiCoresp] ([ContDebit],[ContCredit],[IdJurnal],[Tip],[CodUnitate])
	SELECT Cont AS [ContDebit], 
	(@ContRez) AS [ContCredit],
	[IDJurnal],
	'IC' AS [Tip],
	@CodUnitate
	FROM  [Conturi] a JOIN  (SELECT DISTINCT [IDJurnal] FROM [RulajeDoc] WHERE [CodUnitate]=@CodUnitate) b ON dbo.GetContClass(a.Cont) = '7' AND a.Terminal = 1  AND
	NOT EXISTS(SELECT [IDjurnal],[ContDebit] FROM [ConturiCoresp] x where  [ContDebit]=a.cont 
	and [IDJurnal] = b.[IdJurnal] AND x.[CodUnitate]=@CodUnitate) AND [CodUnitate]=@CodUnitate
	
	DELETE FROM [ConturiCoresp] WHERE 
	NOT EXISTS(
	SELECT [Cont] FROM [Conturi] WHERE Terminal = 1 and [Cont]=[ConturiCoresp].ContDebit and [CodUnitate]=@CodUnitate)
	OR
	NOT EXISTS(
	SELECT [Cont] FROM [Conturi] WHERE Terminal = 1 and [Cont]=[ConturiCoresp].ContCredit  and [CodUnitate]=@CodUnitate)
	
	GOTO Gata
	Error:
	ErrorContCoresp:
		RAISERROR ('Nu a fost definit nici un cont rezultat!',16,-1)
	Gata:
	RETURN
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'SalvareCuAdaugare')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	SalvareCuAdaugare bit NOT NULL CONSTRAINT DF_FaresSetup_SalvareCuAdaugare DEFAULT 0	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'UtilizeazaDepartament')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	UtilizeazaDepartament bit NOT NULL DEFAULT 0	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'CotaTVADetaliuDocument')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	CotaTVADetaliuDocument bit NOT NULL DEFAULT 1	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'RegimFiscalDetaliu')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	RegimFiscalDetaliu bit NOT NULL DEFAULT 0	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'FolosesteValoareCuAdaos')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	FolosesteValoareCuAdaos bit NOT NULL DEFAULT 0	
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[erpforms_FiltreazaParteneri]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[erpforms_FiltreazaParteneri]
GO

CREATE PROCEDURE [dbo].[erpforms_FiltreazaParteneri] 
	-- Add the parameters for the stored procedure here
	@filtru varchar(5000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(8000)
	SET @sel = 'SELECT * FROM [Parteneri] WHERE ' +@filtru 
	EXEC(@sel)
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Parteneri' AND COLUMN_NAME = 'ContClient')
BEGIN
	ALTER TABLE dbo.Parteneri ADD ContClient dbo.TipCont NULL
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaDocumente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaDocumente]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaDocumente] 
	-- Add the parameters for the stored procedure here
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [Documente] where IDDocument in (select [Documente].IDDocument FROM [Documente]  JOIN [DocumenteDetaliu]  ON [Documente].IDDocument = [DocumenteDetaliu].IDDocument WHERE ' +@filtru  + ')'
	EXEC(@sel)
END

GO


IF  NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[fares_DocumenteImportate]') AND type = N'U')
BEGIN
	CREATE TABLE [dbo].[fares_DocumenteImportate](
		[Cheie] [bigint] NOT NULL,
		[Tabela] [varchar](20) NOT NULL,
		[Operatie] [varchar](10) NOT NULL,
		[Data] [datetime] NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
	 CONSTRAINT [PK_fares_DocumenteImportate] PRIMARY KEY CLUSTERED 
	(
		[Cheie] ASC,
		[Tabela] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]


	ALTER TABLE [dbo].[fares_DocumenteImportate]  WITH CHECK ADD  CONSTRAINT [FK_fares_DocumenteImportate_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])

	ALTER TABLE [dbo].[fares_DocumenteImportate] CHECK CONSTRAINT [FK_fares_DocumenteImportate_Unitati]

	ALTER TABLE [dbo].[fares_DocumenteImportate] ADD  CONSTRAINT [DF_fares_DocumenteImportate_Data]  DEFAULT (getdate()) FOR [Data]

	ALTER TABLE [dbo].[fares_DocumenteImportate] ADD  CONSTRAINT [DF_fares_DocumenteImportate_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]

END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Articole' AND COLUMN_NAME = 'EsteServiciu')
BEGIN
	ALTER TABLE dbo.Articole ADD
	EsteServiciu bit NOT NULL CONSTRAINT DF_Articole_EsteServiciu DEFAULT 0
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Documente' AND COLUMN_NAME = 'DataScadenta')
BEGIN
	ALTER TABLE dbo.Documente ADD DataScadenta datetime NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'DocumentePlata' AND COLUMN_NAME = 'DataExtras')
BEGIN
ALTER TABLE dbo.DocumentePlata ADD DataExtras datetime NULL
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_Facturi]'))
DROP VIEW [dbo].[fares_Facturi]
GO

CREATE VIEW [dbo].[fares_Facturi]
AS
SELECT     a.IDPartener, a.NrDoc, a.DataDoc, ROUND(a.Total, 2) AS Total, COALESCE (b.Valoare, 0) AS Valoare, ROUND(a.Total, 2) - COALESCE (b.Valoare, 0) AS RestPlata, 
                      a.DataScadenta, dbo.Parteneri.Denumire, a.IDTipDocument, a.CodUnitate, CAST(a.IDDocument AS varchar(50)) AS IDDocument
FROM         dbo.Documente AS a LEFT OUTER JOIN
                          (SELECT     IDDocument, SUM(Valoare) AS Valoare
                            FROM          dbo.DocumentePlataDetaliu
                            GROUP BY IDDocument) AS b ON a.IDDocument = b.IDDocument INNER JOIN
                      dbo.Parteneri ON a.IDPartener = dbo.Parteneri.IDPartener

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportFacturi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportFacturi]
GO


CREATE PROCEDURE [dbo].[fares_FiltreazaRaportFacturi] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@datasold varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(8000)
	SET @sel = 'SELECT * FROM (SELECT     a.IDPartener, a.NrDoc, a.DataDoc, ROUND(a.Total, 2) AS Total, COALESCE (b.Valoare, 0) AS Valoare, ROUND(a.Total, 2) - COALESCE (b.Valoare, 0) AS RestPlata, 
                      a.DataScadenta, dbo.Parteneri.Denumire, a.IDTipDocument, a.CodUnitate, cast(a.IDDocument AS Varchar(50)) AS IDDocument
		FROM         dbo.Documente AS a LEFT OUTER JOIN
                          (SELECT     IDDocument, SUM(Valoare) AS Valoare
                            FROM          dbo.DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata=dp.IDDocumentPlata WHERE Data<='''+@datasold+'''
                            GROUP BY IDDocument ) AS b ON a.IDDocument = b.IDDocument INNER JOIN
                      dbo.Parteneri ON a.IDPartener = dbo.Parteneri.IDPartener) a WHERE ' + @filtru +
	 ' AND [DataDoc]>=''' + @data1 + ''' AND [DataDoc]<=''' + @data2 +''' ORDER BY [DataDoc]'
	EXEC(@sel)
END

GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Articole' AND COLUMN_NAME = 'ContVenit')
BEGIN
	ALTER TABLE dbo.Articole ADD ContVenit dbo.TipCont NULL
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_TipDocument]'))
DROP VIEW [dbo].[fares_TipDocument]
GO

CREATE VIEW [dbo].[fares_TipDocument]
AS
SELECT     Denumire + '(' + TipOperatie + ')' AS Denumire, TipOperatie, SePlateste, IDTipDocument
FROM         dbo.TipDocument

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'FolosesteObservatii')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD FolosesteObservatii bit NOT NULL CONSTRAINT DF_FaresSetup_FolosesteObservatii DEFAULT 1
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_Plati]'))
DROP VIEW [dbo].[fares_Plati]
GO

CREATE VIEW [dbo].[fares_Plati]
AS
SELECT     dbo.DocumentePlata.Numar, dbo.DocumentePlata.Data, dbo.DocumentePlata.CodUnitate, dbo.DocumentePlata.TipOperatiune, dbo.DocumentePlata.IDTipPlata, 
                      dbo.DocumentePlata.IDPartener, dbo.DocumentePlata.Reprezentand, dbo.DocumentePlata.DataExtras, dbo.Parteneri.Denumire, dbo.DocumentePlataDetaliu.Valoare, 
                      dbo.Documente.NrDoc, dbo.Documente.DataDoc, dbo.Documente.Total, dbo.TipDocumentPlata.Denumire AS TipDoc, dbo.DocumentePlata.IDDocumentPlata, 
                      dbo.Documente.IDDocument
FROM         dbo.DocumentePlata INNER JOIN
                      dbo.DocumentePlataDetaliu ON dbo.DocumentePlata.IDDocumentPlata = dbo.DocumentePlataDetaliu.IDDocumentPlata INNER JOIN
                      dbo.Parteneri ON dbo.DocumentePlata.IDPartener = dbo.Parteneri.IDPartener LEFT OUTER JOIN
                      dbo.Documente ON dbo.DocumentePlataDetaliu.IDDocument = dbo.Documente.IDDocument AND dbo.Parteneri.IDPartener = dbo.Documente.IDPartener INNER JOIN
                      dbo.TipDocumentPlata ON dbo.DocumentePlata.IDTipPlata = dbo.TipDocumentPlata.IDTipPlata

GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportPlati]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportPlati]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportPlati] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_Plati] WHERE ' + @filtru +
	 ' AND [Data]>=''' + @data1 + ''' AND [Data]<=''' + @data2 +''' ORDER BY [Data]'
	EXEC(@sel)
END
GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_DocumenteDimConturi]'))
DROP VIEW [dbo].[fares_DocumenteDimConturi]
GO

CREATE VIEW [dbo].[fares_DocumenteDimConturi]
AS
SELECT     t1.NrDoc, t1.DataDoc, t1.CodUnitate, t1.valoare1, t1.valoare2, t1.valoare3, t1.valoare4, t1.DenArticol, t1.ContDebit, t1.ContCredit, t1.Valoare, t1.Denumire, t1.csd_cont, 
                      t1.csc_cont, t1.IDTipDocument, t1.IDPartener, t1.IDArticol, dbo.GetSintetic(t1.ContDebit) AS SinteticContDebit, dbo.GetSintetic(t1.ContCredit) AS SinteticContCredit, 
                      dbo.GetOrderString(t1.ContDebit) AS OrderContDebit, dbo.GetOrderString(t1.ContCredit) AS OrderContCredit, c1.Denumire AS DenDebit, c2.Denumire AS DenCredit
FROM         (SELECT     a.NrDoc, a.DataDoc, a.CodUnitate, c.valoare1, c.valoare2, c.valoare3, c.valoare4, ar.Denumire AS DenArticol, 
                                              CASE WHEN nc.ContDebit = '{ART.CNT}' THEN ar.Cont WHEN nc.ContDebit = '{TVA.DED}' THEN csd.Cont WHEN nc.ContDebit = '{TVA.COL}' THEN csc.Cont WHEN
                                               nc.ContDebit = '{PRT.CCL}' THEN p.ContClient WHEN nc.ContDebit = '{PRT.CNT}' THEN p.Cont WHEN nc.ContDebit = '{ART.CLT}' THEN ar.ContCheltuiala WHEN
                                               nc.ContDebit = '{ART.VEN}' THEN ar.ContVenit ELSE 'N/A' END AS ContDebit, 
                                              CASE WHEN nc.ContCredit = '{PRT.CNT}' THEN p.Cont WHEN nc.ContCredit = '{PRT.CCL}' THEN p.ContClient WHEN nc.ContCredit = '{TVA.DED}' THEN csd.Cont
                                               WHEN nc.ContCredit = '{TVA.COL}' THEN csc.Cont WHEN nc.ContCredit = '{ART.CLT}' THEN ar.ContCheltuiala WHEN nc.ContCredit = '{ART.VEN}' THEN ar.ContVenit
                                               WHEN nc.ContCredit = '{ART.CNT}' THEN ar.Cont ELSE 'N/A' END AS ContCredit, 
                                              CASE WHEN nc.Valoare = '{DOC.VAL}' THEN dd.ValoareTotala WHEN nc.Valoare = '{DOC.TVA}' THEN dd.ValoareTVA WHEN nc.Valoare = '{DOC.VFTVA}' THEN
                                               dd.ValoareTotala - dd.ValoareTVA WHEN nc.Valoare = '{DOC.PVA}' THEN dd.ValoareTotalaCuAdaos WHEN nc.Valoare = '{DOC.PVAVAL}' THEN (dd.ValoareTotalaCuAdaos
                                               * 100) / (100 + dd.CotaTVA) WHEN nc.Valoare = '{DOC.PVAADS}' THEN (dd.ValoareTotalaCuAdaos * 100) / (100 + dd.CotaTVA) 
                                              - dd.ValoareTotala ELSE 0 END AS Valoare, td.Denumire, csd.Cont AS csd_cont, csc.Cont AS csc_cont, td.IDTipDocument, p.IDPartener, ar.IDArticol
                       FROM          dbo.Documente AS a INNER JOIN
                                              dbo.Parteneri AS p ON a.IDPartener = p.IDPartener INNER JOIN
                                              dbo.TipDocument AS td ON a.IDTipDocument = td.IDTipDocument LEFT OUTER JOIN
                                              dbo.fares_DocumenteDimensiuniLinie AS c ON a.IDDocument = c.IDCheieTabela AND a.CodUnitate = c.CodUnitate INNER JOIN
                                              dbo.DocumenteDetaliu AS dd ON a.IDDocument = dd.IDDocument INNER JOIN
                                              dbo.Articole AS ar ON dd.IDArticol = ar.IDArticol INNER JOIN
                                              dbo.GenNoteContabile AS nc ON nc.IDTipDocument = td.IDTipDocument LEFT OUTER JOIN
                                              dbo.ConturiSpeciale AS csd ON csd.CodUnitate = a.CodUnitate AND csd.Tipuri LIKE '%' + nc.ContDebit + '%' LEFT OUTER JOIN
                                              dbo.ConturiSpeciale AS csc ON csc.CodUnitate = a.CodUnitate AND csc.Tipuri LIKE '%' + nc.ContCredit + '%') AS t1 LEFT OUTER JOIN
                      dbo.Conturi AS c1 ON c1.Cont = t1.ContDebit AND c1.CodUnitate = t1.CodUnitate LEFT OUTER JOIN
                      dbo.Conturi AS c2 ON c2.Cont = t1.ContCredit AND c2.CodUnitate = t1.CodUnitate

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalVertical]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalVertical]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVertical] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumparari smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
docd.*,rf.Denumire AS DenRegim
FROM 
Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
JOIN 

(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
  sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
  SUM(ValoareTVA) As ValoareTVA,
  dd.CotaTVA,dd.IDRegimFiscal
   FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
   GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal) docd ON doc.IDDocument = docd.IDDocument
JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal   
WHERE ((@cumparari=1 AND tipd.IDTipDocument in (0,10)) OR (@cumparari=0 AND tipd.IDTipDocument in (1,11))) AND doc.CodUnitate=@CodUnitate AND 
doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
   ORDER BY DataDoc
END

GO



IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_viewSumeCategorii]'))
DROP VIEW [dbo].[fares_viewSumeCategorii]
GO

CREATE VIEW [dbo].[fares_viewSumeCategorii]
AS
SELECT     dbo.TipCategoriiArticole.Denumire, dbo.Documente.DataDoc, SUM(dbo.DocumenteDetaliu.ValoareTotala) AS ValoareTotala, dbo.DocumenteDetaliu.CodUnitate, 
                      dbo.Articole.Denumire AS DenumireArticol, dbo.Departamente.Nume AS DenDepartament, dbo.Jurnale.Denumire AS DenumireJurnal, dbo.Documente.IDPartener, 
                      dbo.Documente.CodUnitate AS Expr1, dbo.Articole.IDArticol
FROM         dbo.Articole INNER JOIN
                      dbo.DocumenteDetaliu ON dbo.Articole.IDArticol = dbo.DocumenteDetaliu.IDArticol INNER JOIN
                      dbo.TipCategoriiArticole ON dbo.Articole.IDCategorie = dbo.TipCategoriiArticole.IDCategorie INNER JOIN
                      dbo.Documente ON dbo.DocumenteDetaliu.IDDocument = dbo.Documente.IDDocument LEFT OUTER JOIN
                      dbo.Departamente ON dbo.Documente.IDDepartament = dbo.Departamente.IDDepartament LEFT OUTER JOIN
                      dbo.Jurnale ON dbo.Departamente.IDJurnal = dbo.Jurnale.IDJurnal
WHERE     (dbo.Documente.IDTipDocument = 9)
GROUP BY dbo.TipCategoriiArticole.Denumire, dbo.Documente.DataDoc, dbo.DocumenteDetaliu.CodUnitate, dbo.Articole.Denumire, dbo.Departamente.Nume, 
                      dbo.Jurnale.Denumire, dbo.Documente.IDPartener, dbo.Documente.CodUnitate, dbo.Articole.IDArticol

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportArticoleComanda]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportArticoleComanda]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportArticoleComanda] 
	-- Add the parameters for the stored procedure here
	@data1 varchar(20),
	@data2 varchar(20),
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_viewSumeCategorii] WHERE ' + @filtru +
	 ' AND [DataDoc]>=''' + @data1 + ''' AND [DataDoc]<=''' + @data2 +''' ORDER BY [DataDoc]'
	EXEC(@sel)
END


GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Unitati' AND COLUMN_NAME = 'Telefon')
BEGIN
	ALTER TABLE dbo.Unitati ADD
		Telefon varchar(50) NULL,
		Fax varchar(50) NULL,
		Reprezentant varchar(150) NULL,
		Email varchar(150) NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'CaleD394')
BEGIN
	ALTER TABLE dbo.FaresSetup ADD
	CaleD394 varchar(250) NULL
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaD394Facturi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaD394Facturi]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaD394Facturi]
	@CodUnitate smallint,
	@DataStart datetime,
	@DataEnd datetime,
	@cumparari bit	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT b.CUI,a.Total,A.TotalValoare,a.TotalValoareTVA FROM [Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener WHERE a.[CodUnitate]=@CodUnitate AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'
	AND ((@cumparari=1 AND a.IDTipDocument=0) OR (@cumparari=0 AND a.IDTipDocument=1))
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaD394Parteneri]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaD394Parteneri]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaD394Parteneri]
	@CodUnitate smallint,
	@DataStart datetime,
	@DataEnd datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT b.CUI,B.Denumire FROM [Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener WHERE a.[CodUnitate]=@CodUnitate AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'

END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaCodArticol]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaCodArticol]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaCodArticol]
	-- Add the parameters for the stored procedure here
	@radacina varchar(10),
	@codNou varchar(20) out
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @CodReturnat varchar(20)
	DECLARE @CodReturnatNumar varchar(20)
	DECLARE @CodReturnatNumarFilt varchar(20)
	DECLARE @valNumar decimal(18,0)
	DECLARE @i int
    -- Insert statements for procedure here
	SELECT @CodReturnat=Max(codarticol) from articole where codarticol like @radacina + '%'
	--PRINT('test:[' + @CodReturnat+ ']')
	if @CodReturnat is null
	begin 
		SET @CodReturnat = @radacina + '001'
	end
	else
	begin
		SET @CodReturnat = SUBSTRING(@CodReturnat,0,10)
		--PRINT('@CodReturnat:[' + @CodReturnat+ ']')
		SET @CodReturnatNumar = REPLACE(@CodReturnat,@radacina,'')
		--PRINT('@CodReturnatNumar:[' + @CodReturnatNumar+ ']')
		SET @valNumar = 0
		SET @i = 1
		SET @CodReturnatNumarFilt = ''
		--PRINT('Inainte de while')
		WHILE @i<= LEN(@CodReturnatNumar)
		BEGIN
			IF SUBSTRING(@CodReturnatNumar,@i,1) >='0' AND SUBSTRING(@CodReturnatNumar,@i,1)<='9'
				SET @CodReturnatNumarFilt = @CodReturnatNumarFilt + SUBSTRING(@CodReturnatNumar,@i,1)
			SET @i=@i+1
		END
		
		--PRINT('@CodReturnatNumarFilt:[' + @CodReturnatNumarFilt+ ']')
		SET @valNumar = CAST(@CodReturnatNumarFilt AS decimal(20,0))
		SET @valNumar = @valNumar + 1
		
		--PRINT('@valNumar:[' + @valNumar+ ']')
		SET @CodReturnat = CAST(@valNumar as varchar)
		--PRINT('@CodReturnat:[' + @CodReturnat+ ']')
		SET @CodReturnat = @radacina + REPLICATE('0',3-LEN(@CodReturnat)) + @CodReturnat
		--PRINT('@CodReturnat:[' + @CodReturnat+ ']')
		WHILE EXISTS(SELECT * from articole where codarticol = @CodReturnat)
		BEGIN
			SET @valNumar = @valNumar + 1
			SET @CodReturnat = CAST(@valNumar as varchar)
			SET @CodReturnat = @radacina + REPLICATE('0',3-LEN(@CodReturnat)) + @CodReturnat
		END

	end
	SET @codNou = @CodReturnat
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenRegistruCasa]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenRegistruCasa]
GO

CREATE PROCEDURE [dbo].[balcon_GenRegistruCasa]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@Cod varchar(50),
	@DeLaCont dbo.TipCont,
	@PanaLaCont dbo.TipCont
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF LEN(@Cod)=0
		SELECT ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc, 
		ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rjd.DataDoc<rd.DataDoc )
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rjd.DataDoc<rd.DataDoc ) 
		 sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [Conturi] ct 
		JOIN [Rulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont)
		JOIN [RulajeDoc] rd ON ruld.IDDocument = rd.IDDocument
		WHERE ct.CodUnitate = @CodUnitate AND rd.DataDoc<=@PanaLaData AND 
		rd.DataDoc>=@DeLaData AND 
		(LEN(@DeLaCont)=0 OR ct.Cont >= @DeLaCont) AND (LEN(@PanaLaCont) = 0 OR ct.Cont<=@PanaLaCont)
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,ct.Cont
	ELSE
			SELECT ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc, 
		ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rjd.DataDoc<rd.DataDoc )
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rjd.DataDoc<rd.DataDoc ) 
		 sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [Conturi] ct 
		JOIN [ConturiSpeciale] cs ON ct.Cont = cs.Cont AND ct.CodUnitate = cs.CodUnitate AND cs.Tipuri LIKE @Cod
		JOIN [Rulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont)
		JOIN [RulajeDoc] rd ON ruld.IDDocument = rd.IDDocument
		WHERE ct.CodUnitate = @CodUnitate AND rd.DataDoc<=@PanaLaData AND rd.DataDoc>=@DeLaData
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,ct.Cont
		
END
GO

UPDATE t set  data=CAST(CONVERT(varchar(10),data,120) as datetime) from DocumentePlata t
where '00:00:00'<>convert(varchar(8),data,8)
GO

UPDATE t set  [DataScadenta]=CAST(CONVERT(varchar(10),[DataScadenta],120) as datetime) from [Documente] t
where '00:00:00'<>convert(varchar(8),[DataScadenta],8)
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'AfiseazaDoarDocumenteleCurente')
BEGIN
ALTER TABLE dbo.FaresSetup ADD
	AfiseazaDoarDocumenteleCurente bit NOT NULL CONSTRAINT DF_FaresSetup_AfiseazaDoarDocumenteleCurente DEFAULT 0
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_FisaPartenerului]'))
DROP VIEW [dbo].[fares_FisaPartenerului]
GO

CREATE VIEW [dbo].[fares_FisaPartenerului]
AS
SELECT     dbo.TipDocument.Denumire AS TipDocument, dbo.Documente.NrDoc, dbo.Documente.DataDoc, dbo.Documente.Total AS Obligatie, 0 AS Plata, NULL AS DataExtras, 
                      dbo.Documente.IDDocument, dbo.Documente.CodUnitate, dbo.Documente.IDPartener, p.Denumire AS denPartener, dbo.TipDocument.IDTipDocument
FROM         dbo.Documente INNER JOIN
                      dbo.TipDocument ON dbo.Documente.IDTipDocument = dbo.TipDocument.IDTipDocument JOIN
                      Parteneri p ON dbo.Documente.IDPartener = p.IDPartener
UNION
SELECT     b.Denumire AS TipDocument, Numar AS NrDoc, Data AS DataDoc, 0 AS Obligatie, ValoareTotala AS Plata, a.Dataextras, IDDocument, a.codUnitate, a.IDPartener, 
                      p.Denumire AS denPartener,b.IDTipPlata+1000 AS IDTipDocument
FROM         DocumentePlata a JOIN
                      TipDocumentPlata b ON a.IDTipPlata = b.IDTipPlata JOIN
                      DocumentePlataDetaliu c ON c.IDDocumentPlata = a.IDDocumentPlata JOIN
                      Parteneri p ON a.IDPartener = p.IDPartener

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_FiltreazaRaportFisaPartenerului]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_FiltreazaRaportFisaPartenerului]
GO

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportFisaPartenerului] 
	@data2 varchar(20),
	@filtru varchar(5000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [fares_FisaPartenerului] WHERE ' + @filtru +
	 ' AND [DataDoc]<=''' + @data2 +''' ORDER BY [DataDoc]'
	EXEC(@sel)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[balcon_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN

DELETE FROM [ParamListe] WHERE [CodUnitate] = @CodUnitate AND [Host]<>'0'
IF @@ERROR <>0
    GOTO Eroare 


DELETE FROM [Arhive] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [Rulaje] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [RulajeDoc] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [BalconSetup] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
DELETE FROM [SeriiNumerice] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

---initializare bilant           
DELETE FROM [Bilant] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [ConturiSpeciale]WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
DELETE FROM [ArticoleAliniate] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

---Conturi corespondente
DELETE FROM [ConturiCoresp]WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

--conturi
DELETE FROM [Conturi] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

           
DELETE FROM [Jurnale] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 


DELETE FROM [RezultatPatrimonial] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
          
DELETE FROM [VenituriSiCheltuieli] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [ConturiSpeciale] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[fares_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN

PRINT('DocumentePlataDetaliu')
DELETE FROM [DocumentePlataDetaliu] WHERE IDDocumentPlata IN (SELECT IDDocumentPlata FROM [DocumentePlata] WHERE  [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT('DocumentePlata')
DELETE FROM [DocumentePlata] WHERE  [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 



PRINT('DocumenteDetaliu')
DELETE FROM [DocumenteDetaliu] WHERE IDDocument IN (SELECT IDDocument FROM [Documente] WHERE  [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT('[ArticoleParteneri]')
DELETE FROM [ArticoleParteneri] WHERE IDArticol IN (SELECT IDArticol FROM [articole] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 


PRINT('[ArticoleVariante]')
DELETE FROM [ArticoleVariante] WHERE IDArticol IN (SELECT IDArticol FROM [articole] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT('[UMConversie]')
DELETE FROM [UMConversie] WHERE IDArticol IN (SELECT IDArticol FROM [articole] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 


PRINT('Articole')
DELETE FROM [articole] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 


PRINT('Documente')
DELETE FROM [Documente] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare
     
PRINT('ArticoleParteneri')
DELETE FROM [ArticoleParteneri] WHERE IDPartener IN (SELECT IDPartener FROM [Parteneri] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
    
PRINT('[ParteneriConturiBancare]')
DELETE FROM [ParteneriConturiBancare] WHERE IDPartener IN (SELECT IDPartener FROM [Parteneri] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

    
PRINT('Parteneri')
DELETE FROM [Parteneri] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

DELETE FROM [Stocuri] WHERE IDArticol in (SELECT IDArticol FROM [articole] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 



PRINT 'FaresSetup'
DELETE FROM [FaresSetup] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'Trasee'
DELETE FROM [Trasee] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'ValoriDimensiuni'
DELETE FROM [ValoriDimensiuni] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 


COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'IdAngajat') AND
	NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'IdPersoana')
BEGIN
	EXECUTE sp_rename N'dbo.ConcediiMedicale.IdAngajat', N'Tmp_IdPersoana', 'COLUMN' 
	EXECUTE sp_rename N'dbo.ConcediiMedicale.Tmp_IdPersoana', N'IdPersoana', 'COLUMN' 
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PosturiVacante' AND COLUMN_NAME = 'CodUnitate') 
BEGIN
	ALTER TABLE dbo.PosturiVacante ADD
		CodUnitate smallint NOT NULL CONSTRAINT DF_PosturiVacante_CodUnitate DEFAULT 0

	ALTER TABLE dbo.PosturiVacante ADD CONSTRAINT
		FK_PosturiVacante_Unitati FOREIGN KEY
		(
		CodUnitate
		) REFERENCES dbo.Unitati
		(
		CodUnitate
		) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 
END	
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[resuma_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[resuma_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[resuma_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN

PRINT 'Departamente'
DELETE FROM [ContracteMunca] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
    
PRINT '[AngajatiAdaosuri]'
DELETE FROM [AngajatiAdaosuri] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
PRINT '[AngajatiOcupatii]'
DELETE FROM [AngajatiOcupatii] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
PRINT '[AngajatiPontaj]'
DELETE FROM [AngajatiPontaj] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajatiSporuri]'
DELETE FROM [AngajatiSporuri] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajatiSalarizare]'
DELETE FROM [AngajatiSalarizare] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[ConcediiMedicale]'
DELETE FROM [ConcediiMedicale] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
   GOTO Eroare 

PRINT '[ContracteMunca]'
DELETE FROM [ContracteMunca] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
    
PRINT '[PersoaneSuspCM]'
DELETE FROM PersoaneSuspCM WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare     

PRINT '[SituatieMilitara]'
DELETE FROM [SituatieMilitara] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare  
    
PRINT '[SpecialitatiPersoane]'
DELETE FROM [SpecialitatiPersoane] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[CalculSporuri]'
DELETE FROM [CalculSporuri] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[RaportareFoiBoala]'
DELETE FROM [RaportareFoiBoala] WHERE [IDPersoana] IN (SELECT IDPersoana FROM [Persoane] WHERE [CodUnitate] = @CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 



PRINT '[Persoane]'
DELETE FROM [Persoane] WHERE [CodUnitate] = @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[PosturiVacante]'
DELETE FROM [PosturiVacante] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[ResumaSetup]'
DELETE FROM [ResUmaSetup] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 



COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[exbug_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN





    
PRINT '[AngajamentLegalDetaliu]'    
DELETE FROM AngajamentLegalDetaliu WHERE IDAL IN (SELECT IDAL FROM AngajamentLegal WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajamentLegal]'    
DELETE FROM [AngajamentLegal] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajamentBugetarDetaliu]'    
DELETE FROM [AngajamentBugetarDetaliu] WHERE IDAB IN (SELECT IDAB FROM AngajamentBugetar WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajamentBugetarNC]'    
DELETE FROM [AngajamentBugetarNC] WHERE IDAB IN (SELECT IDAB FROM AngajamentBugetar WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[AngajamentBugetar]'    
DELETE FROM [AngajamentBugetar] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'Ordonantari'    
DELETE FROM Ordonantari WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'PropunereAngajareCheltuialaDetaliu'    
DELETE FROM PropunereAngajareCheltuialaDetaliu WHERE IDPac IN (SELECT IDPac FROM PropunereAngajareCheltuiala WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 

PRINT '[PropunereAngajareCheltuiala]'    
DELETE FROM [PropunereAngajareCheltuiala] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'ArticoleAliniate'    
DELETE FROM ArticoleAliniate WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'ResponsabilVizaCFP'
DELETE FROM [ResponsabilVizaCFP] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
    
COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_StergeUnitate]
GO

CREATE PROCEDURE [dbo].[amix_StergeUnitate] 
 @CodUnitate smallint AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN
PRINT('[AmixSetup]')
DELETE FROM [AmixSetup] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
PRINT('[AmixAmortizare]')
DELETE FROM [AmixAmortizare] WHERE IDMijlocFix in (SELECT IDMijlocFix FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
PRINT('[AmixAnsamble]')
DELETE FROM [AmixAnsamble] WHERE IDMFixPrincipal in (SELECT IDMijlocFix FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
PRINT('[AmixComponente] - nu se sterge(view)')
  
PRINT('[AmixComponenteSecundare]')
DELETE FROM [AmixComponenteSecundare] WHERE IDMijlocFix in (SELECT IDMijlocFix FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate)
IF @@ERROR <>0
    GOTO Eroare 
   
PRINT('[AmixDocumente]')
DELETE FROM [AmixDocumente] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare       
      
PRINT('[AmixRegistru]')
DELETE FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
    
PRINT('[AmixRegistruBK]')
DELETE FROM [AmixRegistruBK] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT('nu se sterg [AmixGestionari]')


COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:

END


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StergeUnitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[StergeUnitate]
GO

CREATE PROCEDURE [dbo].[StergeUnitate] 
 @CodUnitate smallint,
 @faraStergereTabelaUnitati bit
 
 
 AS
BEGIN
DECLARE @NrDoc AS int
SET @NrDoc = 0

BEGIN TRAN
IF @CodUnitate=0
	GOTO Eroare2 
EXECUTE amix_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
EXECUTE exbug_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
EXECUTE resuma_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
EXECUTE fares_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 
EXECUTE balcon_StergeUnitate @CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'Departamente'
DELETE FROM [Departamente] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 

PRINT 'SetariLocale'
DELETE FROM [SetariLocale] WHERE [CodUnitate]=@CodUnitate
IF @@ERROR <>0
    GOTO Eroare 


UPDATE [Unitati] SET [UnitateSursa]=0 WHERE [UnitateSursa]=@CodUnitate

IF @faraStergereTabelaUnitati=1
BEGIN
	PRINT 'Unitati'
	DELETE FROM [Unitati] WHERE [CodUnitate]=@CodUnitate
	IF @@ERROR <>0
		GOTO Eroare 
END
    


COMMIT TRAN    
GOTO Gata
Eroare:
  ROLLBACK
  RAISERROR ('Eroare la executia procedurii StergeUnitate',-1,0)
Eroare2:
  ROLLBACK
  RAISERROR ('Unitatea demonstrativa nu se poate sterge!',-1,0)
  
Exista_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine documente!',-1,0)
Exista_Rulaje_Documente:
  ROLLBACK
  RAISERROR ('Unitatea nu poate fi stearsa deoarece contine rulaje - documente!',-1,0)

Gata:
END
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixTipModDocument]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[AmixTipModDocument](
		[IDMod] [smallint] IDENTITY(1,1) NOT NULL,
		[Denumire] [varchar](50) NOT NULL,
		[IDTipDocument] [smallint] NOT NULL,
	 CONSTRAINT [PK_TipModDocumentAmix] PRIMARY KEY CLUSTERED 
	(
		[IDMod] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [IX_TipModDocumentAmix] UNIQUE NONCLUSTERED 
	(
		[IDMod] ASC,
		[IDTipDocument] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[AmixTipModDocument]  WITH CHECK ADD  CONSTRAINT [FK_TipModDocumentAmix_TipDocument] FOREIGN KEY([IDTipDocument])
	REFERENCES [dbo].[TipDocument] ([IDTipDocument])

	ALTER TABLE [dbo].[AmixTipModDocument] CHECK CONSTRAINT [FK_TipModDocumentAmix_TipDocument]

END
GO

IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixTipStare]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[AmixTipStare](
		[IDStare] [smallint] NOT NULL,
		[Denumire] [varchar](50) NOT NULL,
	 CONSTRAINT [PK_AmixTipStare] PRIMARY KEY CLUSTERED 
	(
		[IDStare] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
END
GO

IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixCatalog]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[AmixCatalog](
		[Grupa] [int] NULL,
		[Subgrupa] [int] NULL,
		[Clasa] [int] NULL,
		[Subclasa] [int] NULL,
		[Familia] [nvarchar](3) NULL,
		[DMinAmortizare] [int] NULL,
		[DMaxAmortizare] [int] NULL,
		[ContImobilizare] [varchar](50) NOT NULL,
		[ContAmortizare] [varchar](50) NOT NULL,
		[Denumire] [varchar](150) NOT NULL,
		[IDClasificare] [int] IDENTITY(1,1) NOT NULL,
		[Cod] [varchar](50) NULL,
	 CONSTRAINT [PK_AmixCatalog] PRIMARY KEY CLUSTERED 
	(
		[IDClasificare] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[AmixCatalog] ADD  CONSTRAINT [DF_AmixCatalog_Denumire]  DEFAULT ('') FOR [Denumire]

	ALTER TABLE [dbo].[AmixCatalog] ADD  CONSTRAINT [DF_AmixCatalog_Cod]  DEFAULT ('') FOR [Cod]

END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixGestionari]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AmixGestionari](
	[IDGestionar] [smallint] IDENTITY(1,1) NOT NULL,
	[Nume] [varchar](100) NOT NULL,
 CONSTRAINT [PK_AmixGestionari] PRIMARY KEY CLUSTERED 
(
	[IDGestionar] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixRegistru]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[AmixRegistru](
		[IDMijlocFix] [int] IDENTITY(1,1) NOT NULL,
		[NrInventar] [varchar](20) NOT NULL,
		[Denumire] [varchar](100) NOT NULL,
		[ContAmortizare] [dbo].[TipCont] NULL,
		[StocFaptic] [int] NOT NULL,
		[IDClasificare] [int] NOT NULL,
		[DataIntrare] [datetime] NOT NULL,
		[DataIesire] [datetime] NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
		[IDDepartament] [int] NOT NULL,
		[IDStare] [smallint] NOT NULL,
		[ContImobilizare] [dbo].[TipCont] NULL,
		[ContCheltuiala] [dbo].[TipCont] NULL,
		[DurataAmortizare] [int] NOT NULL,
		[RegimAmortizare] [smallint] NOT NULL,
		[ValoareInventar] [decimal](18, 2) NOT NULL,
		[PunereInFunctiune] [datetime] NOT NULL,
		[Compus] [smallint] NOT NULL,
		[Blocat] [bit] NOT NULL,
		[IDGestionar] [smallint] NOT NULL,
		[ContTransfer] [dbo].[TipCont] NULL,
		[ValoareAmortizata] [decimal](18, 2) NOT NULL,
		[LuniAmortizate] [int] NOT NULL,
		[AmortizareLuna] [decimal](18, 2) NOT NULL,
		[AmortizareAn] [decimal](18, 2) NOT NULL,
		[InFolosinta] [bit] NULL,
		[CantitateOI] [int] NULL,
		[Categorie] [varchar](100) NULL,
	 CONSTRAINT [PK_AmixRegistru] PRIMARY KEY CLUSTERED 
	(
		[IDMijlocFix] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
	 CONSTRAINT [IX_RegistruAmix] UNIQUE NONCLUSTERED 
	(
		[NrInventar] ASC,
		[CodUnitate] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]


	ALTER TABLE [dbo].[AmixRegistru]  WITH CHECK ADD  CONSTRAINT [FK_AmixRegistru_AmixGestionari] FOREIGN KEY([IDGestionar])
	REFERENCES [dbo].[AmixGestionari] ([IDGestionar])

	ALTER TABLE [dbo].[AmixRegistru] CHECK CONSTRAINT [FK_AmixRegistru_AmixGestionari]

	ALTER TABLE [dbo].[AmixRegistru]  WITH NOCHECK ADD  CONSTRAINT [FK_AmixRegistru_Departamente] FOREIGN KEY([IDDepartament])
	REFERENCES [dbo].[Departamente] ([IDDepartament])

	ALTER TABLE [dbo].[AmixRegistru] CHECK CONSTRAINT [FK_RegistruAmix_Departamente]

	ALTER TABLE [dbo].[AmixRegistru]  WITH NOCHECK ADD  CONSTRAINT [FK_RegistruAmix_TipStareMFix] FOREIGN KEY([IDStare])
	REFERENCES [dbo].[AmixTipStare] ([IDStare])

	ALTER TABLE [dbo].[AmixRegistru] CHECK CONSTRAINT [FK_RegistruAmix_TipStareMFix]

	ALTER TABLE [dbo].[AmixRegistru]  WITH NOCHECK ADD  CONSTRAINT [FK_RegistruAmix_Unitati] FOREIGN KEY([CodUnitate])
	REFERENCES [dbo].[Unitati] ([CodUnitate])

	ALTER TABLE [dbo].[AmixRegistru] CHECK CONSTRAINT [FK_RegistruAmix_Unitati]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_Denumire]  DEFAULT ('') FOR [Denumire]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_StocFaptic]  DEFAULT ((1)) FOR [StocFaptic]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_RegistruAmix_IDClasificare]  DEFAULT ((0)) FOR [IDClasificare]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_RegistruAmix_DataIntrare]  DEFAULT (getdate()) FOR [DataIntrare]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_RegistruAmix_DataIesire]  DEFAULT (getdate()) FOR [DataIesire]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_RegistruAmix_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_RegistruAmix_IDDepartament]  DEFAULT ((0)) FOR [IDDepartament]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_RegistruAmix_IDStare]  DEFAULT ((0)) FOR [IDStare]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_DurataAmortizare]  DEFAULT ((0)) FOR [DurataAmortizare]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_RegimAmortizare]  DEFAULT ((0)) FOR [RegimAmortizare]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_ValoareInventar]  DEFAULT ((0)) FOR [ValoareInventar]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_PunereInFunctiune]  DEFAULT (getdate()) FOR [PunereInFunctiune]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_Compus]  DEFAULT ((0)) FOR [Compus]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_Blocat]  DEFAULT ((0)) FOR [Blocat]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_IDGestionar]  DEFAULT ((1)) FOR [IDGestionar]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_ValoareAmortizata]  DEFAULT ((0)) FOR [ValoareAmortizata]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_LuniAmortizate]  DEFAULT ((0)) FOR [LuniAmortizate]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_AmortizareLuna]  DEFAULT ((0)) FOR [AmortizareLuna]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_AmortizareAn]  DEFAULT ((0)) FOR [AmortizareAn]

	ALTER TABLE [dbo].[AmixRegistru] ADD  CONSTRAINT [DF_AmixRegistru_InFolosinta]  DEFAULT ((0)) FOR [InFolosinta]

	ALTER TABLE [dbo].[AmixRegistru] ADD  DEFAULT ((0)) FOR [CantitateOI]

END
GO 

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixDocumente]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[AmixDocumente](
		[IDDocument] [int] IDENTITY(1,1) NOT NULL,
		[NrInventar] [varchar](20) NOT NULL,
		[Denumire] [varchar](100) NOT NULL,
		[IdClasificare] [int] NOT NULL,
		[IDDepartament] [int] NOT NULL,
		[ContAmortizare] [dbo].[TipCont] NULL,
		[ValoareInventar] [decimal](18, 2) NOT NULL,
		[DurataAmortizare] [int] NOT NULL,
		[RegimAmortizare] [smallint] NOT NULL,
		[DataDoc] [datetime] NOT NULL,
		[NumarDoc] [decimal](10, 0) NOT NULL,
		[ComisiaRec] [varchar](100) NOT NULL,
		[DurExtindere] [int] NOT NULL,
		[CodUnitate] [smallint] NOT NULL,
		[IDMod] [smallint] NOT NULL,
		[IDTipDocument] [smallint] NOT NULL,
		[ContCheltuiala] [dbo].[TipCont] NULL,
		[ContImobilizare] [dbo].[TipCont] NULL,
		[Luna] [smallint] NOT NULL,
		[Anul] [smallint] NOT NULL,
		[AmortizareLuna] [decimal](18, 2) NOT NULL,
		[AmortizareAn] [decimal](18, 2) NOT NULL,
		[ValoareAmortizata] [decimal](18, 2) NOT NULL,
		[ContTransfer] [dbo].[TipCont] NULL,
		[IDMijlocFix] [int] NULL,
		[PunereInFunctiune] [datetime] NOT NULL,
		[Compus] [smallint] NOT NULL,
		[AmortizarePrimulAn] [decimal](18, 2) NULL,
		[IDGestionar] [smallint] NOT NULL,
		[ValoareSuplimentara] [decimal](18, 2) NOT NULL,
		[LuniAmortizate] [int] NULL,
		[IDBK] [int] NULL,
		[InFolosinta] [bit] NULL,
		[CantitateOI] [int] NULL,
		[Categorie] [varchar](100) NULL,
	 CONSTRAINT [PK_AmixDocumente] PRIMARY KEY CLUSTERED 
	(
		[IDDocument] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]


	EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Backup-ul registrului' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AmixDocumente', @level2type=N'COLUMN',@level2name=N'IDDocument'

	ALTER TABLE [dbo].[AmixDocumente]  WITH CHECK ADD  CONSTRAINT [FK_AmixDocumente_AmixGestionari] FOREIGN KEY([IDGestionar])
	REFERENCES [dbo].[AmixGestionari] ([IDGestionar])

	ALTER TABLE [dbo].[AmixDocumente] CHECK CONSTRAINT [FK_AmixDocumente_AmixGestionari]

	ALTER TABLE [dbo].[AmixDocumente]  WITH CHECK ADD  CONSTRAINT [FK_DocAmix_DocAmix] FOREIGN KEY([IDDocument])
	REFERENCES [dbo].[AmixDocumente] ([IDDocument])

	ALTER TABLE [dbo].[AmixDocumente] CHECK CONSTRAINT [FK_DocAmix_DocAmix]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_Denumire]  DEFAULT ('') FOR [Denumire]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_IdClasificare]  DEFAULT ((0)) FOR [IdClasificare]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_IDDepartament]  DEFAULT ((0)) FOR [IDDepartament]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_ValoareInventar]  DEFAULT ((0)) FOR [ValoareInventar]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_DurataAmortizare]  DEFAULT ((0)) FOR [DurataAmortizare]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_RegimAmortizare]  DEFAULT ((1)) FOR [RegimAmortizare]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_DataDoc]  DEFAULT (getdate()) FOR [DataDoc]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_ComisiaRec]  DEFAULT ('') FOR [ComisiaRec]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_DurExtindere]  DEFAULT ((0)) FOR [DurExtindere]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_CodUnitate]  DEFAULT ((0)) FOR [CodUnitate]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_IDMod]  DEFAULT ((1)) FOR [IDMod]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_IDTipDocument]  DEFAULT ((0)) FOR [IDTipDocument]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_Luna]  DEFAULT ((0)) FOR [Luna]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_Anul]  DEFAULT ((0)) FOR [Anul]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_AmortizareLuna]  DEFAULT ((0)) FOR [AmortizareLuna]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_AmortizareAn]  DEFAULT ((0)) FOR [AmortizareAn]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_ValoareAmortizata]  DEFAULT ((0)) FOR [ValoareAmortizata]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_PunereInFunctiune]  DEFAULT (getdate()) FOR [PunereInFunctiune]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_Compus]  DEFAULT ((0)) FOR [Compus]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_AmortizarePrimulAn]  DEFAULT ((0)) FOR [AmortizarePrimulAn]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_IDGestionar]  DEFAULT ((1)) FOR [IDGestionar]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_ValoareSuplimentara]  DEFAULT ((0)) FOR [ValoareSuplimentara]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_LuniAmortizate]  DEFAULT ((0)) FOR [LuniAmortizate]

	ALTER TABLE [dbo].[AmixDocumente] ADD  CONSTRAINT [DF_AmixDocumente_InFolosinta]  DEFAULT ((0)) FOR [InFolosinta]

	ALTER TABLE [dbo].[AmixDocumente] ADD  DEFAULT ((0)) FOR [CantitateOI]

END
GO

IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixAmortizare]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[AmixAmortizare](
		[IDMijlocFix] [int] NOT NULL,
		[Luna] [smallint] NOT NULL,
		[An] [smallint] NOT NULL,
		[ValoareInventar] [decimal](18, 2) NOT NULL,
		[ValoareAmortizata] [decimal](18, 2) NOT NULL,
		[ValoareRamasa] [decimal](18, 2) NOT NULL,
		[AmortizareAn] [decimal](18, 2) NOT NULL,
		[AmortizareLuna] [decimal](18, 2) NOT NULL,
		[DurataNormata] [smallint] NOT NULL,
		[LuniAmortizate] [smallint] NOT NULL,
		[Blocat] [bit] NOT NULL,
		[IDBK] [int] NULL,
	 CONSTRAINT [PK_AmixAmortizare] PRIMARY KEY CLUSTERED 
	(
		[IDMijlocFix] ASC,
		[Luna] ASC,
		[An] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[AmixAmortizare]  WITH CHECK ADD  CONSTRAINT [FK_AmixAmortizare_AmixRegistru] FOREIGN KEY([IDMijlocFix])
	REFERENCES [dbo].[AmixRegistru] ([IDMijlocFix])
	ALTER TABLE [dbo].[AmixAmortizare] CHECK CONSTRAINT [FK_AmixAmortizare_AmixRegistru]
	ALTER TABLE [dbo].[AmixAmortizare] ADD  CONSTRAINT [DF_AmixAmortizare_Blocat]  DEFAULT ((0)) FOR [Blocat]
END
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[AmixRegistru]') AND name = N'IX_RegistruAmix')
BEGIN
	ALTER TABLE [dbo].[AmixRegistru] DROP CONSTRAINT [IX_RegistruAmix]

	ALTER TABLE dbo.AmixRegistru ADD CONSTRAINT
		IX_RegistruAmix UNIQUE NONCLUSTERED 
		(
		NrInventar,
		CodUnitate
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixRegistru]') AND type in (N'U'))
BEGIN
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixRegistru' AND COLUMN_NAME = 'InFolosinta')
	BEGIN
		ALTER TABLE dbo.AmixRegistru ADD InFolosinta bit NULL DEFAULT 0
	END
	
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixRegistru' AND COLUMN_NAME = 'CantitateOI')
	BEGIN
		ALTER TABLE dbo.AmixRegistru ADD CantitateOI int NULL DEFAULT 0
	END
	
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixRegistru' AND COLUMN_NAME = 'Categorie')
	BEGIN
		ALTER TABLE dbo.AmixRegistru ADD Categorie varchar(100) NULL 
	END
	
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixDocumente]') AND type in (N'U'))
BEGIN
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixDocumente' AND COLUMN_NAME = 'InFolosinta')
	BEGIN
		ALTER TABLE dbo.AmixDocumente ADD InFolosinta bit NULL DEFAULT 0
	END


	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixDocumente' AND COLUMN_NAME = 'CantitateOI')
	BEGIN
		ALTER TABLE dbo.AmixDocumente ADD CantitateOI int NULL DEFAULT 0

	END
	
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixDocumente' AND COLUMN_NAME = 'Categorie')
	BEGIN
		ALTER TABLE dbo.AmixDocumente ADD Categorie varchar(100) NULL
	END
	
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixRegistruBK]') AND type in (N'U'))
BEGIN

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixRegistruBK' AND COLUMN_NAME = 'InFolosinta')
	BEGIN
		ALTER TABLE dbo.AmixRegistruBK ADD InFolosinta bit NULL DEFAULT 0
	END

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixRegistruBK' AND COLUMN_NAME = 'CantitateOI')
	BEGIN
		ALTER TABLE dbo.AmixRegistruBK ADD CantitateOI int NULL DEFAULT 0
	END

	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AmixRegistruBK' AND COLUMN_NAME = 'Categorie')
	BEGIN
		ALTER TABLE dbo.AmixRegistruBK ADD Categorie varchar(100) NULL 
	END
END	
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewAll]'))
	DROP VIEW [dbo].[AmixViewAll]
GO

CREATE VIEW [dbo].[AmixViewAll]
	AS
	SELECT     dbo.AmixRegistru.NrInventar, dbo.AmixRegistru.Denumire, dbo.AmixRegistru.ContAmortizare, dbo.AmixCatalog.Cod, dbo.AmixRegistru.DataIntrare, 
						  dbo.AmixRegistru.DataIesire, dbo.AmixRegistru.CodUnitate, dbo.Departamente.Nume, dbo.AmixRegistru.ContImobilizare, dbo.AmixRegistru.ContCheltuiala, 
						  dbo.AmixRegistru.DurataAmortizare, dbo.AmixRegistru.RegimAmortizare, dbo.AmixRegistru.ValoareInventar, dbo.AmixRegistru.ValoareAmortizata, 
						  dbo.AmixRegistru.ValoareInventar - dbo.AmixRegistru.ValoareAmortizata AS ValoareRamasa, 0 AS AmortizareLuna, 
						  dbo.AmixRegistru.DurataAmortizare AS DurataNormata, dbo.AmixRegistru.LuniAmortizate, dbo.AmixTipStare.Denumire AS Stare, 0 AS Luna, 0 AS An, 
						  dbo.AmixRegistru.Blocat, dbo.AmixRegistru.Compus, 0 AS AmortizareBlocata, dbo.AmixRegistru.IDMijlocFix, dbo.AmixGestionari.Nume AS Gestionar, 
						  dbo.AmixRegistru.IDStare, dbo.AmixRegistru.StocFaptic, dbo.AmixRegistru.Categorie
	FROM         dbo.AmixRegistru LEFT OUTER JOIN
						  dbo.AmixCatalog ON dbo.AmixRegistru.IDClasificare = dbo.AmixCatalog.IDClasificare INNER JOIN
						  dbo.Departamente ON dbo.AmixRegistru.IDDepartament = dbo.Departamente.IDDepartament INNER JOIN
						  dbo.AmixTipStare ON dbo.AmixRegistru.IDStare = dbo.AmixTipStare.IDStare INNER JOIN
						  dbo.AmixGestionari ON dbo.AmixRegistru.IDGestionar = dbo.AmixGestionari.IDGestionar
GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewDocumente]'))
DROP VIEW [dbo].[AmixViewDocumente]
GO


CREATE VIEW [dbo].[AmixViewDocumente]
AS
SELECT     dbo.AmixDocumente.NumarDoc, dbo.AmixDocumente.DataDoc, dbo.AmixDocumente.IDTipDocument, dbo.TipDocument.Denumire AS DenumireDocument, 
                      dbo.Departamente.Nume AS LocFunctionare, dbo.AmixDocumente.NrInventar, dbo.AmixDocumente.Denumire, dbo.AmixCatalog.Cod AS codClasificare, 
                      dbo.AmixTipModDocument.Denumire AS Modalitate, dbo.AmixDocumente.ContAmortizare, dbo.AmixDocumente.ContCheltuiala, dbo.AmixDocumente.ContImobilizare, 
                      dbo.AmixDocumente.ContTransfer, dbo.AmixDocumente.PunereInFunctiune, dbo.AmixGestionari.Nume AS Gestionar, dbo.AmixDocumente.ValoareInventar, 
                      dbo.AmixDocumente.DurataAmortizare, 
                      CASE WHEN dbo.AmixDocumente.RegimAmortizare = 0 THEN 'Fara Amortizare' WHEN dbo.AmixDocumente.RegimAmortizare = 1 THEN 'Amortizare Liniara' WHEN dbo.AmixDocumente.RegimAmortizare
                       = 2 THEN 'Amortizare Accelerata' WHEN dbo.AmixDocumente.RegimAmortizare = 3 THEN 'Amortizare degresiva' ELSE '' END AS RegimAmortizare, 
                      dbo.AmixDocumente.ComisiaRec AS Observatii, dbo.AmixDocumente.CodUnitate, dbo.AmixDocumente.Luna, dbo.AmixDocumente.Anul, 
                      dbo.AmixDocumente.AmortizareLuna, dbo.AmixDocumente.AmortizareAn, dbo.AmixDocumente.ValoareAmortizata, dbo.AmixDocumente.ValoareSuplimentara, 
                      dbo.AmixDocumente.IDMijlocFix, 
                      CASE WHEN dbo.AmixDocumente.Compus = 0 THEN 'Mijloc Fix' WHEN dbo.AmixDocumente.Compus = 1 THEN 'Mijloc Fix Compus' WHEN dbo.AmixDocumente.Compus
                       = 2 THEN 'Mijloc Fix Componenta' ELSE '' END AS TipMijlocFix, dbo.AmixDocumente.Compus, dbo.AmixDocumente.AmortizarePrimulAn, 
                      dbo.AmixDocumente.Categorie
FROM         dbo.AmixDocumente INNER JOIN
                      dbo.TipDocument ON dbo.AmixDocumente.IDTipDocument = dbo.TipDocument.IDTipDocument LEFT OUTER JOIN
                      dbo.Departamente ON dbo.AmixDocumente.IDDepartament = dbo.Departamente.IDDepartament LEFT OUTER JOIN
                      dbo.AmixCatalog ON dbo.AmixDocumente.IdClasificare = dbo.AmixCatalog.IDClasificare LEFT OUTER JOIN
                      dbo.AmixTipModDocument ON dbo.AmixDocumente.IDMod = dbo.AmixTipModDocument.IDMod LEFT OUTER JOIN
                      dbo.AmixGestionari ON dbo.AmixDocumente.IDGestionar = dbo.AmixGestionari.IDGestionar

GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewIesite]'))
DROP VIEW [dbo].[AmixViewIesite]
GO

CREATE VIEW [dbo].[AmixViewIesite]
AS
SELECT     dbo.AmixRegistru.NrInventar, dbo.AmixRegistru.Denumire, dbo.AmixRegistru.ContAmortizare, dbo.AmixCatalog.Cod, dbo.AmixRegistru.DataIntrare, 
                      dbo.AmixRegistru.DataIesire, dbo.AmixRegistru.CodUnitate, dbo.Departamente.Nume, dbo.AmixRegistru.ContImobilizare, dbo.AmixRegistru.ContCheltuiala, 
                      dbo.AmixRegistru.DurataAmortizare, dbo.AmixRegistru.RegimAmortizare, dbo.AmixRegistru.ValoareInventar, dbo.AmixRegistru.ValoareAmortizata, 
                      0 AS ValoareRamasa, 0 AS AmortizareLuna, dbo.AmixRegistru.DurataAmortizare AS DurataNormata, dbo.AmixRegistru.LuniAmortizate, 
                      dbo.AmixTipStare.Denumire AS Stare, 0 AS Luna, 0 AS An, dbo.AmixRegistru.Blocat, dbo.AmixRegistru.Compus, 0 AS AmortizareBlocata, 
                      dbo.AmixRegistru.IDMijlocFix, dbo.AmixGestionari.Nume AS Gestionar, dbo.AmixRegistru.IDStare, dbo.AmixRegistru.StocFaptic, dbo.AmixRegistru.Categorie
FROM         dbo.AmixRegistru LEFT OUTER JOIN
                      dbo.AmixCatalog ON dbo.AmixRegistru.IDClasificare = dbo.AmixCatalog.IDClasificare INNER JOIN
                      dbo.Departamente ON dbo.AmixRegistru.IDDepartament = dbo.Departamente.IDDepartament INNER JOIN
                      dbo.AmixTipStare ON dbo.AmixRegistru.IDStare = dbo.AmixTipStare.IDStare INNER JOIN
                      dbo.AmixGestionari ON dbo.AmixRegistru.IDGestionar = dbo.AmixGestionari.IDGestionar
WHERE     (dbo.AmixRegistru.IDStare <> 5)

GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewRegistru]'))
DROP VIEW [dbo].[AmixViewRegistru]
GO


CREATE VIEW [dbo].[AmixViewRegistru]
AS
SELECT     dbo.AmixRegistru.NrInventar, dbo.AmixRegistru.Denumire, dbo.AmixRegistru.ContAmortizare, dbo.AmixCatalog.Cod, dbo.AmixRegistru.DataIntrare, 
                      dbo.AmixRegistru.DataIesire, dbo.AmixRegistru.CodUnitate, dbo.Departamente.Nume, dbo.AmixRegistru.ContImobilizare, dbo.AmixRegistru.ContCheltuiala, 
                      dbo.AmixRegistru.DurataAmortizare, dbo.AmixRegistru.RegimAmortizare, dbo.AmixAmortizare.ValoareInventar, dbo.AmixAmortizare.ValoareAmortizata, 
                      dbo.AmixAmortizare.ValoareRamasa, dbo.AmixAmortizare.AmortizareLuna, dbo.AmixAmortizare.DurataNormata, dbo.AmixAmortizare.LuniAmortizate, 
                      dbo.AmixTipStare.Denumire AS Stare, dbo.AmixAmortizare.Luna, dbo.AmixAmortizare.An, dbo.AmixRegistru.Blocat, dbo.AmixRegistru.Compus, 
                      dbo.AmixAmortizare.Blocat AS AmortizareBlocata, dbo.AmixRegistru.IDMijlocFix, dbo.AmixGestionari.Nume AS Gestionar, dbo.AmixRegistru.IDStare, 
                      dbo.AmixRegistru.StocFaptic, dbo.AmixRegistru.Categorie
FROM         dbo.AmixAmortizare INNER JOIN
                      dbo.AmixRegistru ON dbo.AmixAmortizare.IDMijlocFix = dbo.AmixRegistru.IDMijlocFix LEFT OUTER JOIN
                      dbo.AmixCatalog ON dbo.AmixRegistru.IDClasificare = dbo.AmixCatalog.IDClasificare INNER JOIN
                      dbo.Departamente ON dbo.AmixRegistru.IDDepartament = dbo.Departamente.IDDepartament INNER JOIN
                      dbo.AmixTipStare ON dbo.AmixRegistru.IDStare = dbo.AmixTipStare.IDStare INNER JOIN
                      dbo.AmixGestionari ON dbo.AmixRegistru.IDGestionar = dbo.AmixGestionari.IDGestionar

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CompuneCont]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[CompuneCont]
GO

CREATE FUNCTION [dbo].[CompuneCont]
(
	-- Add the parameters for the function here
	@sintetic decimal(6,0),
	@analitic1 decimal(6,0),
	@analitic2 decimal(6,0),
	@analitic3 decimal(6,0),
	@analitic4 decimal(6,0),
	@analitic5 decimal(6,0)
	
)
RETURNS varchar(50)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar varchar(50)
	-- Add the T-SQL statements to compute the return value here
	--SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
    if @sintetic<>0
		set @ResultVar = LTRIM(RTRIM(cast(@sintetic as varchar(6))))
	if @analitic1<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic1 as varchar(6))))	
	if @analitic2<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic2 as varchar(6))))
	if @analitic3<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic3 as varchar(6))))
	if @analitic4<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic4 as varchar(6))))	
	if @analitic5<>0
		set @ResultVar = @ResultVar + '.' + LTRIM(RTRIM(cast(@analitic5 as varchar(6))))
	-- Return the result of the function
	RETURN @ResultVar

END

GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetContParinte]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetContParinte]
GO

CREATE FUNCTION [dbo].[GetContParinte]
(
	-- Add the parameters for the function here
	@sintetic decimal(6,0),
	@analitic1 decimal(6,0),
	@analitic2 decimal(6,0),
	@analitic3 decimal(6,0),
	@analitic4 decimal(6,0),
	@analitic5 decimal(6,0)
	
)
RETURNS varchar(50)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar varchar(50)
	SET @ResultVar = 'N/A'
	-- Add the T-SQL statements to compute the return value here
	--SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>
	if @analitic1<>0
	begin
		set @ResultVar = LTRIM(RTRIM(cast(@sintetic as varchar(6))))	
		if @analitic2<>0
		begin
			set @ResultVar = @ResultVar +'.'+ LTRIM(RTRIM(cast(@analitic1 as varchar(6))))
			if @analitic3<>0
			begin
				set @ResultVar = @ResultVar +'.'+ LTRIM(RTRIM(cast(@analitic2 as varchar(6))))
				if @analitic4<>0
				begin
					set @ResultVar = @ResultVar +'.'+ LTRIM(RTRIM(cast(@analitic3 as varchar(6))))	
					if @analitic5<>0
						set @ResultVar = @ResultVar +'.' +LTRIM(RTRIM(cast(@analitic4 as varchar(6))))
				end
			end
		end
	end	
	-- Return the result of the function
	RETURN @ResultVar

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_ImportBalansW]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_ImportBalansW]
GO


CREATE PROCEDURE [dbo].[balcon_ImportBalansW]
	-- Add the parameters for the stored procedure here
	@ServerName varchar(50), 
	@DBName varchar(50),
	@CodUnitate smallint,
	@CodNod bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @comanda varchar(5000)
    DECLARE @strCodUnitate varchar(10)
    DECLARE @strCodNod varchar(20)
	
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaveArt]') AND type in (N'U'))
		DROP TABLE [dbo].[SaveArt]

	BEGIN TRAN
	
	SET @strCodUnitate = cast(@CodUnitate as varchar(10))
	SET @strCodNod = cast(@CodNod as varchar(20))
   	
    SET @Comanda = 'DELETE FROM [ConturiSpeciale] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [ConturiCoresp] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [RulajeDoc] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [Rulaje] WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	

	
	--Salveaza articolele si legatura cu conturile
	SET @Comanda = 'SELECT [IDArticol],[Cont],[ContCheltuiala],[ContVenit] INTO [SaveArt] FROM [Articole]  WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	
	SET @Comanda = 'UPDATE [Articole] SET [Cont]=''N/A'',[ContCheltuiala]=''N/A'',[ContVenit]=''N/A'' WHERE [CodUnitate] =' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	


    SET @Comanda = 'DELETE FROM [Conturi] WHERE [CodUnitate] =' + @strCodUnitate  + ' AND [Cont]<>''N/A'''
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Sterge Conturi - ok')

	SET @Comanda = 'INSERT INTO [Conturi]
           ([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[CodUnitate])
		SELECT dbo.CompuneCont([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]) [Cont],LTRIM(RTRIM([Denumire])) [Denumire],[Functie],[Terminal],'+
		'dbo.GetContParinte([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]) [ContParinte],'+
		'[DebInit],[CreInit],[DebPrec],[CrePrec],CASE WHEN [ModelContabil]=''F'' THEN 0 WHEN [ModelContabil]=''X'' THEN 2 ELSE 3 END [Categorie],[Debit],[Credit],'+@strCodUnitate+' FROM '
	IF @ServerName <> ''
	BEGIN	
		SET @Comanda = @Comanda + '[' + @ServerName+'].'
	END	
	SET @Comanda = @Comanda +'['+@DBName+'].[dbo].[Cont] WHERE [Tip]=''S'' AND CodNod='  + @strCodNod   
    PRINT(@Comanda)
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

	DECLARE @index smallint
	SET @index = 1

	WHILE @index<=5
	BEGIN
		SET @Comanda = 'INSERT INTO [Conturi]
			   ([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[CodUnitate])
			SELECT dbo.CompuneCont([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]),LTRIM(RTRIM([Denumire])),[Functie],[Terminal],'+
			'dbo.GetContParinte([Sintetic],[Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5]),'+
			'[DebInit],[CreInit],[DebPrec],[CrePrec],CASE WHEN [ModelContabil]=''F'' THEN 0 WHEN [ModelContabil]=''X'' THEN 2 ELSE 3 END [Categorie],[Debit],[Credit],'+@strCodUnitate+' FROM '
		IF @ServerName <> ''
		BEGIN	
			SET @Comanda = @Comanda + '[' + @ServerName+'].'
		END	
		SET @Comanda = @Comanda +'['+@DBName+'].[dbo].[Cont] WHERE [Tip]=''A'+CAST(@index AS varchar(1))+''' AND CodNod='  + @strCodNod   
	    
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE
		SET @index=@index+1	
	END
	
	--,[ContCheltuiala]=a.[ContCheltuiala],[ContVenit]=a.[ContVenit]
	PRINT('Import Conturi - ok')	
	SET @Comanda = 'UPDATE [Articole] SET [Cont]=a.[Cont] FROM [SaveArt] a  WHERE a.IDArticol = Articole.IDarticol ' +
	'AND [CodUnitate] =' + @strCodUnitate  + ' AND EXISTS(SELECT * FROM [Conturi] b WHERE a.[Cont]=b.[Cont] AND [CodUnitate] =' + @strCodUnitate+' )'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	
	
	PRINT('Import Conturi Articole- ok')

    SET @Comanda = 'UPDATE [Conturi] SET [EsteAnalitic]=0 WHERE LEN(LTRIM(RTRIM([Cont])))<=4'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    
    SET @index = 1

	WHILE @index<=5
	BEGIN
		SET @Comanda = 'INSERT INTO [ArticoleAliniate]
			   ([ArtAl],[Denumire],[AAParinte],[Terminal],[Titlu],[Categorii],[TermDenumire],[StartDenumire],[Anexa6],[CodUnitate])
		SELECT dbo.CompuneCont([Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5],0),LTRIM(RTRIM(a.[Denumire])) Denumire'+
		',dbo.GetContParinte([Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5],0) [AAParinte],[Terminal] ,e.Denumire [Titlu],'
		
		IF @index=1
			SET @Comanda = @Comanda + '''[CLT]'''
		ELSE	
			SET @Comanda = @Comanda + '''[TOT]'''
		SET @Comanda = @Comanda +  ' [Categorii],aduna [TermDenumire],'''' [StartDenumire], 0 [Anexa6],'+@strCodUnitate+'  FROM '
		IF @ServerName <> ''
		BEGIN	
			SET @Comanda = @Comanda + '[' + @ServerName+'].'
		END	
		SET @Comanda = @Comanda + '['+@DBName+'].[dbo].[ExBCoduri] a JOIN '
		
		IF @ServerName <> ''
		BEGIN	
			SET @Comanda = @Comanda + '[' + @ServerName+'].'
		END	
		SET @Comanda = @Comanda + '['+@DBName+'].[dbo].[ExBTitluri] e ON a.CodTitlu = e.CodTitlu WHERE ' +
		 ' NOT EXISTS(SELECT * FROM [ArticoleAliniate] b WHERE dbo.CompuneCont([Analitic1],[Analitic2],[Analitic3],[Analitic4],[Analitic5],0)=b.[ArtAl] AND [CodUnitate] =' + @strCodUnitate+' )' +
		 ' AND Tip=''A'+CAST(@index AS varchar(1))+''' AND Analitic1<>0'
		PRINT(@Comanda) 
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE
		SET @index=@index+1		
	END
	
	
	PRINT('ArticoleAliniarte - ok')
    
    /*SET @Comanda = 'SET IDENTITY_INSERT [Parteneri] ON;INSERT INTO [Parteneri]
           ([IDPartener],[Denumire],[CodFiscal],[IDBanca],[ContBancar],[Cont],[CodUnitate],[CUI])
		SELECT		[IDFurnizor],[Denumire],[CodFiscal],0,[ContBancar],[Cont],'+@strCodUnitate+',[IDFurnizor] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Furnizori] '
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Parteneri - ok')

    SET @Comanda = 'INSERT INTO [ConturiSpeciale]
           ([Cont],[Tipuri],[Descriere],[CodUnitate])
			SELECT [Cont],[Tipuri],[Descriere],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[ConturiSpeciale]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('ConturiSpeciale - ok')
	
    SET @Comanda = 'DELETE FROM [Jurnale] WHERE [CodUnitate]=' + @strCodUnitate 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Parteneri] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	*/

    /*SET @Comanda = 'SET IDENTITY_INSERT [Jurnale] ON;INSERT INTO [Jurnale]
           ([IdJurnal],[Denumire]
           ,[IDParinte]
           ,[Codificare]
           ,[Terminal],[CodUnitate])
SELECT [IdJurnal],[Denumire]
           ,[IDParinte]
           ,[Codificare]
           ,[Terminal],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Jurnale] WHERE [IDJurnal]<>[IdParinte]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Jurnale - ok')

    SET @Comanda = 'SET IDENTITY_INSERT [Jurnale] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Ordonantari] ON;INSERT INTO [Ordonantari]
           ([IDOrdonantare],[NrDoc]
           ,[DataDoc]
           ,[ArtAl]
           ,[IDFurnizor]
           ,[ValoareLei]
           ,[IDJurnal]
           ,[Scop]
           ,[DocJust]
           ,[AngLegal]
           ,[Achitat],[CodUnitate])
SELECT [IDOrdonantare],[NrDoc]
           ,[DataDoc]
           ,SUBSTRING(REPLACE([Cont],''7701.'',''''),1,40)
           ,[IDFurnizor]
           ,[ValoareLei]
           ,[IDJurnal]
           ,[Scop]
           ,[DocJust]
           ,[AngLegal]
           ,[Achitat],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Ordonantari]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Ordonantari - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [Ordonantari] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	



    SET @Comanda = 'SET IDENTITY_INSERT [RulajeDoc] ON;INSERT INTO [RulajeDoc]
           ([IDDocument],[ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate],[CodUnitate])
SELECT [IDDocument],[ValoareLei]
           ,[ValoareValuta]
           ,[NumarDoc]
           ,[DataDoc]
           ,[NumarNota]
           ,[TipFormula]
           ,[IDJurnal]
           ,[Observatii]
           ,[xUser]
           ,[xHost]
           ,[xDate],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[RulajeDoc]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('RulajeDoc - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [RulajeDoc] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'SET IDENTITY_INSERT [Rulaje] ON;INSERT INTO [Rulaje]
           ([CodFormula]
      ,[ContDebit]
      ,[ContCredit]
      ,[CodValuta]
      ,[IDDocument]
      ,[ValoareValuta]
      ,[Curs]
      ,[ValoareLei]
      ,[DataDocJust]
      ,[TipDocJust]
      ,[NumarDocJust]
      ,[ExplicatiiDocJust]
      ,[NrCrt],[CodUnitate])
SELECT [CodFormula]*1000
      ,[ContDebit]
      ,[ContCredit]
      ,[CodValuta]
      ,[IDDocument]
      ,[ValoareValuta]
      ,[Curs]
      ,[ValoareLei]
      ,[DataDocJust]
      ,[TipDocJust]
      ,[NumarDocJust]
      ,[ExplicatiiDocJust]
      ,[NrCrt],'+@strCodUnitate+' FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Rulaje]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('Rulaje - ok')
    SET @Comanda = 'SET IDENTITY_INSERT [Rulaje] OFF'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	

    SET @Comanda = 'DELETE FROM [RulajeDoc] WHERE NOT EXISTS(SELECT * FROM [Rulaje] a 
 WHERE a.IdDocument=[RulajeDoc].IDDocument)'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
*/
 	COMMIT



	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la preluarea datelor!',16,-1)
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:
	
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'ContVenitArticolImplicit')
BEGIN
ALTER TABLE dbo.FaresSetup ADD
	ContVenitArticolImplicit [dbo].[TipCont]  NULL 
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewToate]'))
DROP VIEW [dbo].[AmixViewToate]
GO

CREATE VIEW [dbo].[AmixViewToate]
AS
SELECT * FROM dbo.AmixViewRegistru
UNION
SELECT * FROM AmixViewIesite

GO
